Question

sql分组合并统计查询

数据库用sql2000，查询出第一表的结果，谢谢。感谢 rgcsm 和 llkklk 的回答，两种方法我都已测试，查询结果是对的，还有两个问题，1、如何统计查询出的结果，见表一底部，2、加入时间条件，按每月，表中显示为1月，如果查2月?

Answer 1

select ta.deptName, count(ta.deptName) as personCount, sum(ta.status1) as status1, sum(ta.status2) as status2, sum(ta.status3) as status3, sum(ta.status4) as status4 from
(select deptName, case status when '状态1' then 1 else 0 end as status1, case status when '状态2' then 1 else 0 end as status2, case status when '状态3' then 1 else 0 end as status3, case status when '状态4' then 1 else 0 end as status4 from tableName where datepart(y, date)=2011 and datepart(m, date)=1) ta group by ta.deptName
月份需要加上年份一起判断, 合计不能从这条语句中得到,可以通过另外一条语句或者通过程序中对数据分析得到,不知道你前台用什么开发的,是不是有控件可以直接生成.
select count(*) as recordCount, sum(tb.status1) as status1, sum(tb.status2) as status2, sum(tb.status3) as status3, sum(tb.status4) as status4 from (select ta.deptName, count(ta.deptName) as personCount, sum(ta.status1) as status1, sum(ta.status2) as status2, sum(ta.status3) as status3, sum(ta.status4) as status4 from
(select deptName, case status when '状态1' then 1 else 0 end as status1, case status when '状态2' then 1 else 0 end as status2, case status when '状态3' then 1 else 0 end as status3, case status when '状态4' then 1 else 0 end as status4 from tableName where datepart(y, date)=2011 and datepart(m, date)=1) ta group by ta.deptName) tb
这条语句可以得到合计值

Answer 2

select 部门,count(姓名) 人数,(select count(*) from biao where 部门=a.部门 and 状态='状态1') as 状态1,(select count(*) from biao where 部门=a.部门 and 状态='状态2') as 状态2,(select count(*) from biao where 部门=a.部门 and 状态='状态3') as 状态3,(select count(*) from biao where 部门=a.部门 and 状态='状态4') as 状态4 from biao a group by 部门