求两张表查询的SQL语句,该如何处理

求两张表查询的SQL语句
两张表T1，T2
T1中2个字段为 ComId，height
T2中2个字段为 ComId，width

T1中数据如下
ComId height
A1 oo
A1 hh

T2中数据如下
ComId width
A1 T2
A1 T3
A1 T4

希望得到如下查询结果
ComId Height Width
A1 00 T2
hh T3
T4

希望高人出招。

------解决方案--------------------

SQL code

use test
go
create  table #t1(ComId char(2),height char(2))
insert #t1 select 'A1','oo' 
insert #t1 select 'A1','hh' 
go
create  table #t2(ComId char(2),width char(2)) 
insert #t2 select 'A1','T2' 
insert #t2 select 'A1','T3' 
insert #t2 select 'A1','T4'
go

select *,row=1 into #a from #t1 order by ComId
select *,row=1 into #b from #t2 order by ComId
go
declare @ComId char(2),@i int
update #a
set @i=case when ComId=@ComId then @i+1 else 1 end,row=@i,@ComId=ComId

set @i=0
update #b
set @i=case when ComId=@ComId then @i+1 else 1 end,row=@i,@ComId=ComId
go
select
    [ComId]=case when a.row=(select min(row) from #b where ComId=b.ComId) then b.ComId else ''end,
    [height]=isnull(a.height,''),
    b.width
from 
    #b b 
left join
    #a a on b.ComId=a.ComId and b.row=a.row
/*
ComId height width 
----- ------ ----- 
A1    oo     T2
      hh     T3
             T4

（所影响的行数为 3 行）

*/

求两张表查询的SQL语句,该如何处理

相关推荐