一个表中有下以字段,现需要按下的格式统计输出,如何写sql语句?1.表结构:
表 名:DutyRecord
表字段: ID UserNo UserName DutyDate DutyStatu
1 0001 张三 2008-08-01 正常
2 0002 李四 2008-08-02 正常
3 0003 王小路 2008-08-01 正常
4 0004 李于 2008-08-02 正常
5 0005 高天乐 2008-08-01 病假
6 0002 李四 2008-09-02 正常
7 0003 王小路 2008-01-01 正常
8 0002 李四 2008-07-02 正常
9 0003 王小路 2008-08-05 正常
10 0002 李四 2008-08-05 缺席
11 0003 王小路 2008-08-05 婚假 ....2.统计结果: 08月份
UserNo UserName DutyDate 正常 病假 缺席 婚假 迟到 总计天数
0001 张三 2008-08 1 0 0 0 0 1
0002 李四 2008-08 2 0 1 0 0 4
0003 王小路 2008-08 2 0 0 1 0 33.统计SQL 语句怎么写(Sql server)??我的分不多,但是全给上,第一次在CSDN里面问问题,很急,给出正确答案马上散分.谢谢~~
表 名:DutyRecord
表字段: ID UserNo UserName DutyDate DutyStatu
1 0001 张三 2008-08-01 正常
2 0002 李四 2008-08-02 正常
3 0003 王小路 2008-08-01 正常
4 0004 李于 2008-08-02 正常
5 0005 高天乐 2008-08-01 病假
6 0002 李四 2008-09-02 正常
7 0003 王小路 2008-01-01 正常
8 0002 李四 2008-07-02 正常
9 0003 王小路 2008-08-05 正常
10 0002 李四 2008-08-05 缺席
11 0003 王小路 2008-08-05 婚假 ....2.统计结果: 08月份
UserNo UserName DutyDate 正常 病假 缺席 婚假 迟到 总计天数
0001 张三 2008-08 1 0 0 0 0 1
0002 李四 2008-08 2 0 1 0 0 4
0003 王小路 2008-08 2 0 0 1 0 33.统计SQL 语句怎么写(Sql server)??我的分不多,但是全给上,第一次在CSDN里面问问题,很急,给出正确答案马上散分.谢谢~~
sum(case DutyStatu when '病假' then 1 else '0' end) as '病假',
sum(case DutyStatu when '缺席' then 1 else '0' end) as '缺席',
sum(case DutyStatu when '婚假' then 1 else '0' end) as '婚假',
sum(case DutyStatu when '迟到' then 1 else '0' end) as '迟到',
from DutyRecord
where dataPart(DutyDate,'MM') ='08'
group by userno,username,dataPart(DutyDate,'MM')
select userno,username,datePart(month,cast(DutyDate as datetime)) as Month,
sum(case when DutyStatu='正常' then 1 else 0 end) as '正常',
sum(case when DutyStatu='病假' then 1 else 0 end) as '病假',
sum(case when DutyStatu='缺席' then 1 else 0 end) as '缺席',
sum(case when DutyStatu='婚假' then 1 else 0 end) as '婚假',
sum(case when DutyStatu='迟到' then 1 else 0 end) as '迟到'
from DutyRecord
where datePart(month,cast(DutyDate as datetime)) ='08'
group by userno,username,datePart(month,cast(DutyDate as datetime))
测试过的
UserNo UserName DutyDate 正常 病假 缺席 婚假 迟到 总计天数
0001 张三 2008-08 1 0 0 0 0 1
0002 李四 2008-08 1 0 1 0 0 4
0002 李四 2008-08 1 0 0 0 0 4
0003 王小路 2008-08 2 0 0 1 0 3
select userno,username,cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2)) as DutyDate,
sum(case when DutyStatu='正常' then 1 else 0 end) as '正常',
sum(case when DutyStatu='病假' then 1 else 0 end) as '病假',
sum(case when DutyStatu='缺席' then 1 else 0 end) as '缺席',
sum(case when DutyStatu='婚假' then 1 else 0 end) as '婚假',
sum(case when DutyStatu='迟到' then 1 else 0 end) as '迟到',
count(dutystatu) as 总天数
from DutyRecord
where cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2)) ='2008-8'
group by userno,username,
cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2))
最后的答案
/*创建表
create table DutyRecord
(id int identity(1,1),
UserNo varchar(10),
UserName varchar(20),
DutyDate varchar(50),
DutyStatu char(4))
*/
/*插入数据
insert into DutyRecord(UserNo,Username,dutydate,dutystatu)select '0001', '张三', '2008-08-01' , '正常'
union all
select '0002', '李四', '2008-08-02' , '正常'
union all
select '0003', '王小路', '2008-08-01' , '正常'
union all
select '0004', '李于', '2008-08-02' , '正常'
union all
select '0005', '高天乐', '2008-08-01' , '病假'
union all
select '0002', '李四', '2008-09-02' , '正常'
union all
select '0003', '王小路', '2008-01-01' , '正常'
union all
select '0002', '李四', '2008-07-02' , '正常'
union all
select '0003', '王小路', '2008-08-05' , '正常'
union all
select '0002', '李四' , '2008-08-05' ,'缺席'
union all
select '0003', '王小路' , '2008-08-05' , '婚假'
*/select userno,username,cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2)) as DutyDate,
sum(case when DutyStatu='正常' then 1 else 0 end) as '正常',
sum(case when DutyStatu='病假' then 1 else 0 end) as '病假',
sum(case when DutyStatu='缺席' then 1 else 0 end) as '缺席',
sum(case when DutyStatu='婚假' then 1 else 0 end) as '婚假',
sum(case when DutyStatu='迟到' then 1 else 0 end) as '迟到',
count(dutystatu) as 总天数
from DutyRecord
where cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2)) ='2008-8'
group by userno,username,
cast(datePart(year,cast(DutyDate as datetime)) as varchar(4)) + '-' + cast(datePart(month,cast(DutyDate as datetime))as varchar(2))/*
结果
userno username dutydate 正常 病假 缺席 婚假 迟到 总天数
0001 张三 2008-8 1 0 0 0 0 1
0002 李四 2008-8 1 0 1 0 0 2
0003 王小路 2008-8 2 0 0 1 0 3
0004 李于 2008-8 1 0 0 0 0 1
0005 高天乐 2008-8 0 1 0 0 0 1
*/