软复杂的数据统计!(SOS)

Question

select
b.ID,
b.Name,
sum(case when starttime>'8:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'12:00' then 1 else 0 end) as 未登记
from 
b 
left join
a on b.ID=a.ID
group by b.ID,
b.Name

Answer 1

select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >='12:00' then 1 else 0 end) as 早退,
sum(case when a.starttime <'12:00' or a.starttime is null then 1 else 0 end)+
sum(case when a.endtime <'12:00' or a.endtime is null then 1 else 0 end) as 未登记   
from b left join a on b.id=a.id
group by b.id,b.name

Answer 2

还是没对，改下select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >='12:00' then 1 else 0 end) as 早退,
sum(case when a.starttime <'12:00' or a.starttime is null then 1 else 0 end)+
sum(case when a.endtime <'12:00' or a.endtime is null then 1 else 0 end) as 未登记   
from b cross join ( select distinct date  from a) as t
left join a on b.id=a.id
group by b.id,b.name

Answer 3

Haiwer 还是不对 结果如下:1          张三       3 3 9
2          李四       0 3 6
3          王五       0 0 18
4          赵六       0 0 6总共就三天最多未登记数也就是6次

Answer 4

再修正：select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >='12:00' then 1 else 0 end) as 早退,
sum(case when a.starttime <'12:00' or a.starttime is null then 1 else 0 end)+
sum(case when a.endtime <'12:00' or a.endtime is null then 1 else 0 end) as 未登记   
from b cross join ( select distinct date  from a) as t
left join a on b.id=a.id and t.date=a.date
group by b.id,b.name

Answer 5

select ID,Name,
sum(case when starttime>'8:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'12:00' then 1 else 0 end) as 未登记
from 
(select a.id as id,b.name as name,min(starttime) as starttime,max(endtime) as endtime
 from a,b where a.id = b.id group by a.id,b.name) c

Answer 6

漏了日期。
select ID,Name,
sum(case when starttime>'8:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'12:00' then 1 else 0 end) as 未登记
from 
(select a.id as id,b.name,a.date as name,min(starttime) as starttime,max(endtime) as endtime
 from a,b where a.id = b.id group by a.id,b.name,a.date) c

Answer 7

漏了group by 
select ID,Name,
sum(case when starttime>'8:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'12:00' then 1 else 0 end) as 未登记
from 
(select a.id as id,b.name,a.date as name,min(starttime) as starttime,max(endtime) as endtime
 from a,b where a.id = b.id group by a.id,b.name,a.date) c
group by id,name

Answer 8

谢谢你的回复JiangHongTao :报错:
服务器: 消息 8156，级别 16，状态 1，行 1
多次为 'c' 指定了列 'name'。

Answer 9

select ID,Name,
sum(case when starttime>'8:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'12:00' then 1 else 0 end) as 未登记
from 
(select a.id as id,b.name,a.date as date/*name这儿有问题,重新起一个别的名字*/,min(starttime) as starttime,max(endtime) as endtime
 from a,b where a.id = b.id group by a.id,b.name,a.date) c
group by id,name

Answer 10

修正JiangHongTao 后结果如下:1          张三       0 1 0
2          李四       0 1 0
3          王五       0 3 3还是不对呀

Answer 11

create table a (id int,date  char(10),starttime char( 5),endtime char(5))
insert  a select 1,                       '2007-12-27',     '08:09',             '20:17' 
union all select 2 ,                      '2007-12-27' ,    '08:10' ,            '18:10' 
union all select 3  ,                     '2007-12-27'  ,   '08:11'  ,           '08:11' 
union all select 1   ,                    '2007-12-28'   ,  '08:35'   ,          '20:17' 
union all select 2    ,                   '2007-12-28'    , '08:10'    ,         '18:10' 
union all select 3     ,                  '2007-12-28'     ,'08:19'     ,        '08:19' 
union all select 1      ,                 '2007-12-29'     ,'08:09'      ,       '17:17' 
union all select 2       ,                '2007-12-29'     ,'17:10'       ,      '17:10' 
union all select 3        ,               '2007-12-29'     ,'08:19'        ,     '08:19' 
create table b(ID int,name  varchar(10))
insert  b select 1,       '张三' 
union all select 2 ,      '李四' 
union all select 3  ,     '王五' 
union all select 4   ,    '赵六'
select b.ID,Name,
sum(case when starttime>'08:30' then 1 else 0 end) as 迟到,
sum(case when endtime<'17:30' then 1 else 0 end) as 早退,
6+sum(case when endtime>'12:00' then -1 else 0 end + case when starttime<'12:00' then -1 else 0 end) as 未登记
from b left join a on a.id = b.id group by b.id,b.name order by b.id
/*
ID          Name       迟到          早退          未登记         
----------- ---------- ----------- ----------- ----------- 
1           张三         1           1           0
2           李四         1           1           1
3           王五         0           3           3
4           赵六         0           0           6
*/
drop table a,b
 

Answer 12

create table a (id int,date  char(10),starttime char( 5),endtime char(5))
insert  a select 1,                       '2007-12-27',     '08:09',             '20:17' 
union all select 2 ,                      '2007-12-27' ,    '08:10' ,            '18:10' 
union all select 3  ,                     '2007-12-27'  ,   '08:11'  ,           '08:11' 
union all select 1   ,                    '2007-12-28'   ,  '08:35'   ,          '20:17' 
union all select 2    ,                   '2007-12-28'    , '08:10'    ,         '18:10' 
union all select 3     ,                  '2007-12-28'     ,'08:19'     ,        '08:19' 
union all select 1      ,                 '2007-12-29'     ,'08:09'      ,       '17:17' 
union all select 2       ,                '2007-12-29'     ,'17:10'       ,      '17:10' 
union all select 3        ,               '2007-12-29'     ,'08:19'        ,     '08:19' 
create table b(ID int,name  varchar(10))
insert  b select 1,       '张三' 
union all select 2 ,      '李四' 
union all select 3  ,     '王五' 
union all select 4   ,    '赵六'
select b.ID,Name,
sum(case when starttime between '08:31' and '11:59' then 1 else 0 end) as 迟到,
sum(case when endtime between '12:01' and '17:30' then 1 else 0 end) as 早退,
6+sum(case when endtime>'12:00' then -1 else 0 end + case when starttime<'12:00' then -1 else 0 end) as 未登记
from b left join a on a.id = b.id group by b.id,b.name order by b.id
/*
ID          Name       迟到          早退          未登记         
----------- ---------- ----------- ----------- ----------- 
1           张三         1           1           0
2           李四         0           1           1
3           王五         0           0           3
4           赵六         0           0           6
*/
drop table a,b
 

Answer 13

一不小心select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >='12:00' then 1 else 0 end) as 早退,
sum(case when a.starttime >'12:00' or a.starttime is null then 1 else 0 end)+
sum(case when a.endtime <'12:00' or a.endtime is null then 1 else 0 end) as 未登记   
from b cross join ( select distinct date  from a) as t
left join a on b.id=a.id and t.date=a.date
group by b.id,b.name

Answer 14

修正 Haiwer 如下:select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >'12:00' then 1 else 0 end) as 早退,
sum(case when  a.starttime is null or a.starttime>'12:00' then 1 else 0 end)+
sum(case when  a.endtime is null or a.endtime<'12:00' then 1 else 0 end) as 未登记   
from b cross join ( select distinct date  from a) as t
left join a on b.id=a.id and t.date=a.date
group by b.id,b.name order by b.id
1                   张三             1 1 0 
2                   李四             0 1 1 
3                   王五             0 0 3 
4                   赵六             0 0 6 可以达到要求,再请教一下各位高手如果我要统计的日期是动态变化的咋办

Answer 15

select b.ID,Name,
sum(case when starttime between '08:31' and '11:59' then 1 else 0 end) as 迟到,
sum(case when endtime between '12:01' and '17:29' then 1 else 0 end) as 早退,
(select 2*count(distinct date) from a)+sum(case when endtime>'12:00' then -1 else 0 end + case when starttime<'12:00' then -1 else 0 end) as 未登记
from b left join a on a.id = b.id group by b.id,b.name order by b.id

Answer 16

非常感谢JiangHongTao 和Haiwer 两位的回复.再问两位如果我要查询统计的日期间隔是动态变化的呢?望大侠帮忙!

Answer 17

declare @begin char(10),@end char(10)
select @begin='2007-12-27',@end = '2007-12-29'
select b.ID,Name,
sum(case when starttime between '08:31' and '11:59' then 1 else 0 end) as 迟到,
sum(case when endtime between '12:01' and '17:29' then 1 else 0 end) as 早退,
(select 2*count(distinct date) from a where date between @begin and @end)+sum(case when endtime>'12:00' then -1 else 0 end + case when starttime<'12:00' then -1 else 0 end) as 未登记
from b left join a on a.id = b.id 
where a.date between @begin and @end
group by b.id,b.name order by b.id

Answer 18

declare @starttime varchar(10)
declare @endtime varchar(10)
set @starttime = '2007-12-27'
set @endtime = '2007-12-30'--select datediff(d,cast(@starttime as datetime),cast(@endtime as datetime))select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >='12:00' then 1 else 0 end) as 早退,
2*(datediff(d,cast(@starttime as datetime),cast(@endtime as datetime))+ 1 )+
sum(case when a.starttime < '12:00' then -1 else 0 end)+
sum(case when a.endtime >'12:00' then -1 else 0 end) as 未登记   
from b left join a on b.id=a.id and a.date between @starttime and @endtime
group by b.id,b.name
order by b.id

Answer 19

select b.ID,b.name,
sum(case when a.starttime >'08:30' and a.starttime <='12:00' then 1 else 0 end) as 迟到,
sum(case when a.endtime <'17:30' and a.endtime >'12:00' then 1 else 0 end) as 早退,
sum(case when  a.starttime is null or a.starttime>'12:00' then 1 else 0 end)+
sum(case when  a.endtime is null or a.endtime<'12:00' then 1 else 0 end) as 未登记   
from b cross join ( select distinct date  from a 
where date>='2008-2-1' and date<'2008-3-1'           --这里加条件就可以了
) as t
left join a on b.id=a.id and t.date=a.date
group by b.id,b.name order by b.id