急求解决一个sql的有关问题
急求解决一个sql的问题。
请教个问题 有两个sql语句
--------------------------------
select tname,count(1) count1 from
(
select * from table1 t1 where t1.sid=0
)
group by tname
---------------------------------------
select tname,count(1) count2 from
(
select * from table1 t1 where t1.sid>0
)
group by tname
-----------------------------------
这两条记录 分别为：
tname count1
XXXX 2
YYYY 5
-----------------
tname count2
XXXX 3
ZZZZ 7
我希望通过一条sql语句查询最得到的最后结果是
sectionname count1 count2
XXXX 2 3
YYYY 5 0
ZZZZ 0 7
------解决方案--------------------
------解决方案--------------------
请教个问题 有两个sql语句
--------------------------------
select tname,count(1) count1 from
(
select * from table1 t1 where t1.sid=0
)
group by tname
---------------------------------------
select tname,count(1) count2 from
(
select * from table1 t1 where t1.sid>0
)
group by tname
-----------------------------------
这两条记录 分别为：
tname count1
XXXX 2
YYYY 5
-----------------
tname count2
XXXX 3
ZZZZ 7
我希望通过一条sql语句查询最得到的最后结果是
sectionname count1 count2
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
*/