日期:2014-05-16 浏览次数:20665 次
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
*/