新手提问这里有三个表 project,mfg(厂商)和PN(元器件)
project 中有ID和ProjectName
mfg中有ID,ProjectID
PN中有mfgID,ProjectID和StateID
现在想统计出这样的效果
ProjectName mfgCout(mfg个数) PNCount(PN总数) PNState1Count(状态1下的PN个数) PNState2Count(状态2下的PN个数) PNState3Count(状态3下的PN个数)各位提一下解决方案,谢谢
project 中有ID和ProjectName
mfg中有ID,ProjectID
PN中有mfgID,ProjectID和StateID
现在想统计出这样的效果
ProjectName mfgCout(mfg个数) PNCount(PN总数) PNState1Count(状态1下的PN个数) PNState2Count(状态2下的PN个数) PNState3Count(状态3下的PN个数)各位提一下解决方案,谢谢
a.ProjectName,count(b.ID) as mfgCout,count(c.StateID) as [PNCount],
sum(case c.StateID when 1 else 0 end) as PNState1Count,
sum(case c.StateID when 2 else 0 end) as PNState2Count,
sum(case c.StateID when 3 else 0 end) as PNState3Count
from
project a,mfg b ,pn c
where
a.id=b.ProjectID
and
b.ID=c.mfgID
group by
a.ProjectName
如果要是统计两个状态的pn之和呢,比如再加上一列PNState1&2Count