34,590
社区成员
发帖
与我相关
我的任务
分享初到CSDN，求大家帮忙
大家好，小弟受一问题困扰很久，特来CSDN求助，谢谢大家，在线结贴！^..^
---------------
问题：有下面数据表，求一查询语句。
数据表如下：（有省略）
----------------------------------------------------------
id 员工编号 姓名 分公司 部门 刷卡日期 刷卡时间
3135 00068 罗1 广州公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:28:56.000
3136 00188 李2 深圳公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:29:58.000
3137 00305 刘3 广州公司 前厅部 2011-10-11 00:00:00.000 1900-01-01 16:35:22.000
3138 00260 骆4 北京公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:35:49.000
3139 00241 苏5 北京公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:36:22.000
3140 00257 李6 广州公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:37:30.000
3141 00025 李7 深圳公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:39:07.000
-----------------------------------------------------------
上面是一个大型公司的员工消费刷卡数据表，刷卡一次说明用餐一次。
id：是主键。
分公司：代表各分公司，每个分公司都有保安部、客房部等。
部门：是具体的部门。
刷卡日期和刷卡时间分别存放了日期和时间。
-----------------------------------------
我现在想做一张统计表，统计每个月每个分公司每个部门早晨（8点到10点）、中餐（12点到13点）、晚餐（6点到7点）的用餐次数。例如：
----
分公司 部门 早餐次数 中餐次数 晚餐次数 合计
深圳分公司 保安部 7 8 5 20
谢谢大家，周末愉快！
---------------
问题：有下面数据表，求一查询语句。
数据表如下：（有省略）
----------------------------------------------------------
id 员工编号 姓名 分公司 部门 刷卡日期 刷卡时间
3135 00068 罗1 广州公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:28:56.000
3136 00188 李2 深圳公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:29:58.000
3137 00305 刘3 广州公司 前厅部 2011-10-11 00:00:00.000 1900-01-01 16:35:22.000
3138 00260 骆4 北京公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:35:49.000
3139 00241 苏5 北京公司 客房部 2011-10-11 00:00:00.000 1900-01-01 16:36:22.000
3140 00257 李6 广州公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:37:30.000
3141 00025 李7 深圳公司 保安部 2011-10-11 00:00:00.000 1900-01-01 16:39:07.000
-----------------------------------------------------------
上面是一个大型公司的员工消费刷卡数据表，刷卡一次说明用餐一次。
id：是主键。
分公司：代表各分公司，每个分公司都有保安部、客房部等。
部门：是具体的部门。
刷卡日期和刷卡时间分别存放了日期和时间。
-----------------------------------------
我现在想做一张统计表，统计每个月每个分公司每个部门早晨（8点到10点）、中餐（12点到13点）、晚餐（6点到7点）的用餐次数。例如：
----
分公司 部门 早餐次数 中餐次数 晚餐次数 合计
深圳分公司 保安部 7 8 5 20
谢谢大家，周末愉快！
...全文
请发表友善的回复…
发表回复
aa43402614 2011-10-30
- 打赏
- 举报
我的天，这么多热心的人。。。。100分不够分啊。。。感谢大家
-晴天 2011-10-30
- 打赏
- 举报
create table tb(id int,员工编号 varchar(10),姓名 varchar(10),分公司 varchar(20),部门 varchar(20) , 刷卡日期 datetime,刷卡时间 datetime)
insert into tb select 3135 ,'00068', '罗1', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:28:56.000'
insert into tb select 3136 ,'00188', '李2', '深圳公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:29:58.000'
insert into tb select 3137 ,'00305', '刘3', '广州公司', '前厅部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:22.000'
insert into tb select 3138 ,'00260', '骆4', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:49.000'
insert into tb select 3139 ,'00241', '苏5', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:36:22.000'
insert into tb select 3140 ,'00257', '李6', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:37:30.000'
insert into tb select 3141 ,'00025', '李7', '深圳公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:39:07.000'
go
select 分公司,部门,
SUM(case when CONVERT(varchar(5),刷卡时间,108) between '08:00' and '10:00' then 1 else 0 end)早餐,
SUM(case when CONVERT(varchar(5),刷卡时间,108) between '12:00' and '13:00' then 1 else 0 end)午餐,
SUM(case when CONVERT(varchar(5),刷卡时间,108) between '18:00' and '19:00' then 1 else 0 end)晚餐,
count(*) 合计
from tb
group by 分公司,部门
--这个公司的人都在规定时间外用餐!
/*
分公司 部门 早餐 午餐 晚餐 合计
-------------------- -------------------- ----------- ----------- ----------- -----------
广州公司 保安部 0 0 0 2
深圳公司 保安部 0 0 0 1
北京公司 客房部 0 0 0 2
深圳公司 客房部 0 0 0 1
广州公司 前厅部 0 0 0 1
(5 行受影响)
*/
go
drop table tb
中国风 2011-10-30
- 打赏
- 举报
或用 count+case
select
分公司 ,部门,
count(case when datepart(hh,刷卡时间) between 8 and 10 then 1 end) as 早餐次数,
count(case when datepart(hh,刷卡时间) between 12 and 13 then 1 end) as 中餐次数,
count(case when datepart(hh,刷卡时间) between 18 and 19 then 1 end) as 晚餐次数,
count(1) as 合计
from
tb
group by
分公司 ,部门
--小F-- 2011-10-30
- 打赏
- 举报
select
分公司 ,部门,
sum(case when datepart(hh,刷卡时间) between 8 and 10 then 1 else 0 end) as 早餐次数,
sum(case when datepart(hh,刷卡时间) between 12 and 13 then 1 else 0 end) as 中餐次数,
sum(case when datepart(hh,刷卡时间) between 18 and 19 then 1 else 0 end) as 晚餐次数,
count(1) as 合计
from
tb
group by
分公司 ,部门dawugui 2011-10-30
- 打赏
- 举报
如果你说的8点到9点是指'08:00:00' - '09:00:00'则如下:
create table tb(id int,员工编号 varchar(10),姓名 varchar(10),分公司 varchar(20),部门 varchar(20) , 刷卡日期 datetime,刷卡时间 datetime)
insert into tb values(3135 ,'00068', '罗1', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:28:56.000')
insert into tb values(3136 ,'00188', '李2', '深圳公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:29:58.000')
insert into tb values(3137 ,'00305', '刘3', '广州公司', '前厅部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:22.000')
insert into tb values(3138 ,'00260', '骆4', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:49.000')
insert into tb values(3139 ,'00241', '苏5', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:36:22.000')
insert into tb values(3140 ,'00257', '李6', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:37:30.000')
insert into tb values(3141 ,'00025', '李7', '深圳公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:39:07.000')
go
select 分公司 ,部门,
sum(case when convert(varchar(8),刷卡时间,120) between '08:00:00' and '09:00:00' then 1 else 0 end) [早餐次数],
sum(case when convert(varchar(8),刷卡时间,120) between '12:00:00' and '13:00:00' then 1 else 0 end) [中餐次数],
sum(case when convert(varchar(8),刷卡时间,120) between '18:00:00' and '19:00:00' then 1 else 0 end) [晚餐次数],
sum(case when convert(varchar(8),刷卡时间,120) not between '08:00:00' and '09:00:00' and
convert(varchar(8),刷卡时间,120) not between '12:00:00' and '13:00:00' and
convert(varchar(8),刷卡时间,120) not between '18:00:00' and '19:00:00'
then 1 else 0 end) [其他],
count(1) 合计
from tb
group by 分公司 ,部门
drop table tb
/*
分公司 部门 早餐次数 中餐次数 晚餐次数 其他 合计
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------
广州公司 保安部 0 0 0 2 2
深圳公司 保安部 0 0 0 1 1
北京公司 客房部 0 0 0 2 2
深圳公司 客房部 0 0 0 1 1
广州公司 前厅部 0 0 0 1 1
（所影响的行数为 5 行）
*/dafu10000 2011-10-30
- 打赏
- 举报
select 分公司 ,部门,cast(刷卡日期 as char(7)) as 年月
sum(case when datepart(hh,刷卡时间) between 8 and 10 then 1 else 0 end) [早餐次数],
sum(case when datepart(hh,刷卡时间) between 12 and 13 then 1 else 0 end) [中餐次数],
sum(case when datepart(hh,刷卡时间) between 18 and 19 then 1 else 0 end) [晚餐次数],
count(1) 合计
from tb
group by 分公司 ,部门,cast(刷卡日期 as char(7))
dawugui 2011-10-30
- 打赏
- 举报
你给的数据中存在不是你说的那三段时间的点,我把他定义为其他.
create table tb(id int,员工编号 varchar(10),姓名 varchar(10),分公司 varchar(20),部门 varchar(20) , 刷卡日期 datetime,刷卡时间 datetime)
insert into tb values(3135 ,'00068', '罗1', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:28:56.000')
insert into tb values(3136 ,'00188', '李2', '深圳公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:29:58.000')
insert into tb values(3137 ,'00305', '刘3', '广州公司', '前厅部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:22.000')
insert into tb values(3138 ,'00260', '骆4', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:49.000')
insert into tb values(3139 ,'00241', '苏5', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:36:22.000')
insert into tb values(3140 ,'00257', '李6', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:37:30.000')
insert into tb values(3141 ,'00025', '李7', '深圳公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:39:07.000')
go
select 分公司 ,部门,
sum(case when datepart(hh,刷卡时间) between 8 and 10 then 1 else 0 end) [早餐次数],
sum(case when datepart(hh,刷卡时间) between 12 and 13 then 1 else 0 end) [中餐次数],
sum(case when datepart(hh,刷卡时间) between 18 and 19 then 1 else 0 end) [晚餐次数],
sum(case when datepart(hh,刷卡时间) not between 8 and 10 and datepart(hh,刷卡时间) not between 12 and 13 and datepart(hh,刷卡时间) not between 18 and 19 then 1 else 0 end) [其他],
count(1) 合计
from tb
group by 分公司 ,部门
drop table tb
/*
分公司 部门 早餐次数 中餐次数 晚餐次数 其他 合计
-------------------- -------------------- ----------- ----------- ----------- ----------- -----------
广州公司 保安部 0 0 0 2 2
深圳公司 保安部 0 0 0 1 1
北京公司 客房部 0 0 0 2 2
深圳公司 客房部 0 0 0 1 1
广州公司 前厅部 0 0 0 1 1
（所影响的行数为 5 行）
*/dawugui 2011-10-30
- 打赏
- 举报
create table tb(id int,员工编号 varchar(10),姓名 varchar(10),分公司 varchar(20),部门 varchar(20) , 刷卡日期 datetime,刷卡时间 datetime)
insert into tb values(3135 ,'00068', '罗1', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:28:56.000')
insert into tb values(3136 ,'00188', '李2', '深圳公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:29:58.000')
insert into tb values(3137 ,'00305', '刘3', '广州公司', '前厅部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:22.000')
insert into tb values(3138 ,'00260', '骆4', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:35:49.000')
insert into tb values(3139 ,'00241', '苏5', '北京公司', '客房部', '2011-10-11 00:00:00.000', '1900-01-01 16:36:22.000')
insert into tb values(3140 ,'00257', '李6', '广州公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:37:30.000')
insert into tb values(3141 ,'00025', '李7', '深圳公司', '保安部', '2011-10-11 00:00:00.000', '1900-01-01 16:39:07.000')
go
select 分公司 ,部门,
sum(case when datepart(hh,刷卡时间) between 8 and 10 then 1 else 0 end) [早餐次数],
sum(case when datepart(hh,刷卡时间) between 12 and 13 then 1 else 0 end) [中餐次数],
sum(case when datepart(hh,刷卡时间) between 18 and 19 then 1 else 0 end) [晚餐次数],
count(1) 合计
from tb
group by 分公司 ,部门
drop table tb
/*
分公司 部门 早餐次数 中餐次数 晚餐次数 合计
-------------------- -------------------- ----------- ----------- ----------- -----------
广州公司 保安部 0 0 0 2
深圳公司 保安部 0 0 0 1
北京公司 客房部 0 0 0 2
深圳公司 客房部 0 0 0 1
广州公司 前厅部 0 0 0 1
（所影响的行数为 5 行）
*/另:建议刷卡日期,刷卡时间这两个字段可以合二为一,不需要两个字段.
dawugui 2011-10-30
- 打赏
- 举报
select 分公司 ,部门,
sum(case when datepart(hh,刷卡时间) between 8 and 10 then 1 else 0 end) [早餐次数],
sum(case when datepart(hh,刷卡时间) between 12 and 13 then 1 else 0 end) [中餐次数],
sum(case when datepart(hh,刷卡时间) between 18 and 19 then 1 else 0 end) [晚餐次数],
count(1) 合计
from tb
group by 分公司 ,部门
