sql 统计问题请教

来源：百度知道 编辑：UC知道 时间：2024/05/31 20:54:12
在做对帐单项目时碰到一个统计问题,情况如下:
需要通过按照机构ID统计出该机构下的帐单各种状态数量
数据库结构如下:
idbank,docstate
行名 ,帐单状态
希望得到如下结果:
行名}A状态}B状态}C状态}D状态}E状态}F状态
A行 | 4 | 5 | 6 | 7 | 8 | 9
请各位帮帮忙....
谢谢回答,你的SQL得到的记过集是象这样的:
idbank,docstate,count(docstate)
A行 , A状态 ,5
你这样是统计的共有多少种状态吧,,,我是需要得到各种状态下的数量....谢谢!

我这样写了一条语句不知道对不对，有什么地方需要完善的吗?
select state4.idbank,state4.cou4,state5.cou5,state6.cou6,state7.cou7 from
(select idbank,count(idbank) as cou4 from checkmaindata where docstate=4 group by idbank) state4 left join
(select idbank,count(idbank) as cou5 from checkmaindata where docstate=5 group by idbank) state5 on state4.idbank = state5.idbank left join

(select idbank,count(idbank) as cou6 from checkmaindata where docstate=6 group by idbank) state6 on state4.idbank = state6.idbank left join
(select idbank,count(idbank) as cou7 from checkmaindata where docstate=7 group by idbank) state7 on state4.idbank = state7.

select idbank, count(case when docstate='a' then docstate end) as a,
count(case when docstate='b' then docstate end) as b,
count(case when docstate='c' then docstate end) as c,
count(case when docstate='d' then docstate end) as d,
count(case when docstate='e' then docstate end) as e,
count(case when docstate='f' then docstate end) as f
from 表 group by idbank
这样OK？

select idbank,docstate,count(docstate) from 表 group by idbank,docstate;

(select idbank,docstate,count(docstate) from table1 group by idbank,docstate where idbank="ICBC")
unoin
(select idbank,docstate,count(docstate) from table1 group by idbank,docstate where idbank="CBC")
.
.
.
.