select a.tname,isnull(b.count1,0)as count1,isnull(c.count2,0)as count2 from ( select distinct tname from table1 where sid>=0 ) a left join (select x.tname,count(1) count1 from ( select * from table1 t1 where t1.sid=0 ) x group by x.tname )b on a.tname=b.tname left join ( select y.tname,count(1) count2 from ( select * from table1 t1 where t1.sid>0 ) y group by y.tname ) c on a.tname=c.tname
select a.tname,count1=max(a.count1),count2=MAX(a.count2) from ( select tname,count(1) count1,count2=ISNULL(null,0) from ( select * from table1 t1 where t1.sid=0 ) group by tname union all select tname,ISNULL(null,0),count(1) count2 from ( select * from table1 t1 where t1.sid>0 ) group by tname ) a group by a.tname/* XXXX 2 3 YYYY 5 0 ZZZZ 0 7 */
select a.tname,isnull(b.count1,0)as count1,isnull(c.count2,0)as count2 from
(
select distinct tname from table1 where sid>=0
) a left join
(select x.tname,count(1) count1 from
(
select * from table1 t1 where t1.sid=0
) x
group by x.tname
)b on a.tname=b.tname
left join
(
select y.tname,count(1) count2 from
(
select * from table1 t1 where t1.sid>0
) y
group by y.tname
) c on a.tname=c.tname
select a.tname,count1=max(a.count1),count2=MAX(a.count2)
from (
select tname,count(1) count1,count2=ISNULL(null,0) from
(
select * from table1 t1 where t1.sid=0
)
group by tname
union all
select tname,ISNULL(null,0),count(1) count2 from
(
select * from table1 t1 where t1.sid>0
)
group by tname
) a
group by a.tname/*
XXXX 2 3
YYYY 5 0
ZZZZ 0 7
*/