如上图，我想用前两个表组合查询出第三个表，怎么做？
------解决方案--------------------
select bb.tdid,aa.tname,sum(case when bb.state=2 then 1 else 0 end )as atate 为2的个数 ,sum(case when bb.state=1 then 1 else 0 end )as atate 为1的个数 from aa left join bb on aa.tid=bb.tid group by bb.tdid,aa.tname
------解决方案--------------------
- SQL code
declare @tabA table(TID int,TName nvarchar(10))insert into @tabAselect 37,N'美丽天涯海' union allselect 38,N'美丽天涯海' union allselect 39,N'美丽天涯海角'declare @tabB table(TDID int,TID int,Status int)insert into @tabBselect 9,38,2 union allselect 11,38,2 union allselect 14,39,1 union allselect 15,39,2 union allselect 16,39,1 union allselect 17,39,2select a.TID,a.TName,sum(case when b.Status=2 then 1 else 0 end) Status为2的个数,sum(case when b.Status=1 then 1 else 0 end) Status为1的个数 from @tabA a,@tabB bwhere a.TID=b.TID group by a.TID,a.TName/*TID TName Status为2的个数 Status为1的个数----------- ---------- ----------- -----------38 美丽天涯海 2 039 美丽天涯海角 2 2*/