求大神帮个忙我现在要统计每天 天餐就餐人数
数据库中的数据是通过考勤机打卡上去的,计算规则如下:
中餐 10:00~13:00
晚餐 16:00~19:00
夜宵 22:00~00:30
同一时段中同一人打卡多次只计一次,
现在我用 distinct [UserID] 可以获取某一时段的人数,如果要用一句语句就同时获取3个时段的就餐人数,要怎么写,试了半天没有试出来
数据库中的数据是通过考勤机打卡上去的,计算规则如下:
中餐 10:00~13:00
晚餐 16:00~19:00
夜宵 22:00~00:30
同一时段中同一人打卡多次只计一次,
现在我用 distinct [UserID] 可以获取某一时段的人数,如果要用一句语句就同时获取3个时段的就餐人数,要怎么写,试了半天没有试出来
from table
where time between 10:00~13:00
or time between 16:00~19:00
or time between 22:00~00:30
from table
where 10:00~13:00
union
select distinct [UserID]
from table
where 16:00~19:00
union
select distinct [UserID]
from table
where 22:00~00:30
from table
where 10:00~13:00
union
select distinct [UserID]
from table
where 16:00~19:00
union
select distinct [UserID]
from table
where 22:00~00:30
---------------------------------
insert into Table_dinner values
('a001','20130304 11:46AM'),
('a001','20130304 11:47AM'),
('a002','20130304 11:46AM'),
('a003','20130304 11:46AM'),
('a004','20130304 11:46AM'),
('a002','20130304 04:46PM'),
('a003','20130304 04:47PM'),
('a003','20130304 04:48PM'),
('a001','20130304 22:48PM'),
('a001','20130304 23:48PM'),
('a001','20130305 00:18AM')
---------------------------------数据截图
------------------------------------------------------------------查找语句
-----------------------------------------------------------
select '11:00~13:00',count(distinct [UserID])
from Table_dinner
where convert(varchar(2),checkTime,108) in('11','12','13')
union
select '16:00~18:00',count(distinct [UserID])
from Table_dinner
where convert(varchar(2),checkTime,108) in('16','17','18')
union
select '22:00~23:30',count(distinct [UserID])
from Table_dinner
where convert(varchar(2),checkTime,108) in('00','22','23')
-----------------------------------------------------------思路和楼上的几位大侠差不多,
-- Author :磊仔
-- Date :2013-03-05 23:53:40
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86)
-- Jun 28 2012 08:42:37
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([UserID] varchar(4),[DTime] datetime)
insert #TA
select 'a001','2013-03-04T11:46:00.000' union all
select 'a001','2013-03-04T11:47:00.000' union all
select 'a002','2013-03-04T11:46:00.000' union all
select 'a003','2013-03-04T11:46:00.000' union all
select 'a004','2013-03-04T11:46:00.000' union all
select 'a002','2013-03-04T16:46:00.000' union all
select 'a003','2013-03-04T16:47:00.000' union all
select 'a003','2013-03-04T16:48:00.000' union all
select 'a001','2013-03-04T22:48:00.000' union all
select 'a001','2013-03-04T23:48:00.000' union all
select 'a001','2013-03-05T00:18:00.000'
--------------开始查询--------------------------
;with cet as
( select '中餐' as Type,'09:29:00' as BgnTime, '12:29:00' as EndTime
union all select '晚餐' as Type,'15:29:00' as BgnTime, '18:29:00' as EndTime
union all select '夜宵' as Type,'21:29:00' as BgnTime, '23:59:00' as EndTime )
select CAST(dateadd(MI,-31,[DTime]) as date) 日期,b.Type 餐别,COUNT(distinct UserID) 人数
from #TA a, cet b
where CAST(dateadd(MI,-31,[DTime]) as time) between b.BgnTime and b.EndTime
group by CAST(dateadd(MI,-31,[DTime]) as date),b.Type ----------------结果----------------------------
/*
(11 行受影响)
日期 餐别 人数
---------- ---- -----------
2013-03-04 晚餐 2
2013-03-04 夜宵 1
2013-03-04 中餐 4(3 行受影响)
*/
2013-3-6 100 88 12时间字段为:CHECKTIME 用户字段:USERID 表名:CHECKINOUT
Thanks!
-- Author :磊仔
-- Date :2013-03-05 23:53:40
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86)
-- Jun 28 2012 08:42:37
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据:CHECKINOUTcreate table CHECKINOUT([UserID] varchar(4),[CHECKTIME] datetime)
insert CHECKINOUT
select 'a001','2013-03-04T11:46:00.000' union all
select 'a001','2013-03-04T11:47:00.000' union all
select 'a002','2013-03-04T11:46:00.000' union all
select 'a003','2013-03-04T11:46:00.000' union all
select 'a004','2013-03-04T11:46:00.000' union all
select 'a002','2013-03-04T16:46:00.000' union all
select 'a003','2013-03-04T16:47:00.000' union all
select 'a003','2013-03-04T16:48:00.000' union all
select 'a001','2013-03-04T22:48:00.000' union all
select 'a001','2013-03-04T23:48:00.000' union all
select 'a001','2013-03-05T00:18:00.000'
--------------开始查询--------------------------
;with cet as
( select '中餐' as Type,'09:29:00' as BgnTime, '12:29:00' as EndTime
union all select '晚餐' as Type,'15:29:00' as BgnTime, '18:29:00' as EndTime
union all select '夜宵' as Type,'21:29:00' as BgnTime, '23:59:00' as EndTime )
,CET1 as
(select CAST(dateadd(MI,-31,[CHECKTIME]) as date) 日期,b.Type 餐别,COUNT(distinct UserID) 人数
from CHECKINOUT a, cet b
where CAST(dateadd(MI,-31,[CHECKTIME]) as time) between b.BgnTime and b.EndTime
group by CAST(dateadd(MI,-31,[CHECKTIME]) as date),b.Type )
select * from CET1 a pivot (max(人数) for 餐别 in([中餐],[晚餐],[夜宵]))p----------------结果----------------------------
/*
日期 中餐 晚餐 夜宵
---------- ----------- ----------- -----------
2013-03-04 4 2 1(1 行受影响)*/
消息 243,级别 16,状态 1,第 2 行
类型 time 不是已定义的系统类型。
消息 243,级别 16,状态 1,第 2 行
类型 time 不是已定义的系统类型。
消息 243,级别 16,状态 1,第 2 行
类型 date 不是已定义的系统类型。
消息 243,级别 16,状态 1,第 2 行
类型 date 不是已定义的系统类型。
union all
select count(distinct userid)as 人数,'晚餐'as 餐类 from 刷卡 where CONVERT (int,datepart(hh,DTime)) between 16 and 19
union all
select count(distinct userid)as 人数,'夜宵'as 餐类 from 刷卡 where datepart(hh,DTime)>=22 or( DATEPART(HH,DTime)<1 and DATEPART(mi,DTime)<=30 )
用磊仔的更好,不过还要考慮多个问题,如果我是今晚12:17分吃的夜宵,那日期会显示到3月7号的,那该分组到哪天呢?
还是 2000?
这段代码需要在2008以上运行
;with cet as
( select '中餐' as Type,'2000-01-01 09:29:00' as BgnTime, '2000-01-01 12:29:00' as EndTime
union all select '晚餐' as Type,'2000-01-01 15:29:00' as BgnTime, '2000-01-01 18:29:00' as EndTime
union all select '夜宵' as Type,'2000-01-01 21:29:00' as BgnTime, '2000-01-01 23:59:00' as EndTime )
,CET1 as
(select CONVERT(VARCHAR(10),dateadd(MI,-31,[CHECKTIME]),120) 日期,b.Type 餐别,COUNT(distinct UserID) 人数
from CHECKINOUT a, cet b
where CAST(STUFF(CONVERT(VARCHAR(19),dateadd(MI,-31,[CHECKTIME]),120),1,10,'2000-01-01') AS DATETIME) between b.BgnTime and b.EndTime
group by CONVERT(VARCHAR(10),dateadd(MI,-31,[CHECKTIME]),120),b.Type )
select * from CET1 a pivot (max(人数) for 餐别 in([中餐],[晚餐],[夜宵]))p----------------结果----------------------------
/*
日期 中餐 晚餐 夜宵
---------- ----------- ----------- -----------
2013-03-04 4 2 1(1 行受影响)*/
http://bbs.csdn.net/topics/390341946