table1
iID iPosID cState
1 2 是
2 2 是
3 2 否
4 3 是
5 3 否table2
iPosID cPosName
1 aa
2 bb
3 cc想实现成
posID cPosName posIDNum cStateNum
1 aa 0 0
2 bb 3 2
3 cc 2 1select posID,
posIDNum=count(*),
cStateNum=sum(case when cState='是' then 1 else 0 end)
from table1,table2
group by posID--result
posID cPosName posIDNum cStateNum
----------- ----------- -----------
2 bb 3 2
3 cc 2 1上面能得出统计的部分值.但我想要把值等0的也显示出来.请指点一下.谢谢.
iID iPosID cState
1 2 是
2 2 是
3 2 否
4 3 是
5 3 否table2
iPosID cPosName
1 aa
2 bb
3 cc想实现成
posID cPosName posIDNum cStateNum
1 aa 0 0
2 bb 3 2
3 cc 2 1select posID,
posIDNum=count(*),
cStateNum=sum(case when cState='是' then 1 else 0 end)
from table1,table2
group by posID--result
posID cPosName posIDNum cStateNum
----------- ----------- -----------
2 bb 3 2
3 cc 2 1上面能得出统计的部分值.但我想要把值等0的也显示出来.请指点一下.谢谢.
posIDNum =count(*),
cStateNum =sum(case when a.cState='是' then 1 else 0 end)
from table1 as a Right Join table2 as b on a.iPosID=b.iPosID
group by b.posID
a.posID,
posIDNum =isnull(count(b.iPosID),0),
cStateNum=sum(case when a.cState='是' then 1 else 0 end)
from
table1 a
left join
table2 b
on
a.iPosID=b.iPosID
group by
a.posID
a.posID,
posIDNum =isnull(count(b.iPosID),0),
cStateNum=sum(case when b.cState='是' then 1 else 0 end)
from
table2 a
left join
table1 b
on
a.iPosID=b.iPosID
group by
a.posID
insert A select 1, 2, '是'
union all select 2, 2, '是'
union all select 3, 2, '否'
union all select 4, 3, '是'
union all select 5, 3, '否'
create table B(iPosID int, cPosName varchar(10))
insert B select 1, 'aa'
union all select 2, 'bb'
union all select 3, 'cc'select B.*, posIDNum=isnull(A.posIDNum, 0),
cStateNum=isnull(A.cStateNum, 0)
from B
left join
(
select iPosID, posIDNum=count(*),
cStateNum=sum(case when cState='是' then 1 else 0 end)
from A
group by iPosID
)A on B.iPosID=A.iPosID--result
iPosID cPosName posIDNum cStateNum
----------- ---------- ----------- -----------
1 aa 0 0
2 bb 3 2
3 cc 2 1(3 row(s) affected)