求考勤查询SQL查询语句或过程

Question

表结构如下： 
ID    ATTTIME                      UserID 
1    2010-01-01 8：10：00            1 
2    2010-01-01 8：15：00            1 
3    2010-01-01 17：10：00            2 
4    2010-01-02 8：05：00            1 
5    2010-01-02 17：00：00            1 
我想得到结果如下： userid      date          montime                  aftertime 
1        2010-01-01      8:10:00                    null 
1        2010-01-02      8：05：00                  17:00:00 
2        2010-01-01        null                    17:10:00  
在线等哇~谢谢各位大大了~！

Answer 1

--还发两个？
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1select * from [tb]
/*
userid      date          montime                  aftertime 
1        2010-01-01      8:10:00                    null 
1        2010-01-02      8：05：00                  17:00:00 
2        2010-01-01        null                    17:10:00  
*/select userid,
convert(varchar(10),[ATTTIME],120) as date,
case datepart(hour,min([ATTTIME])) when 8 then convert(varchar(8),min([ATTTIME]),108) else null end as montime,
case datepart(hour,max([ATTTIME])) when 17 then convert(varchar(8),max([ATTTIME]),108) else null end as aftertime
from [tb]
group by userid,convert(varchar(10),[ATTTIME],120)
order by userid
----------------------------------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-02-04 20:11:42
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1
--------------开始查询--------------------------
select
 userid,
 convert(varchar(10),[ATTTIME],120) as date,
 case datepart(hour,min([ATTTIME])) when 8 then convert(varchar(8),min([ATTTIME]),108) else null end as montime,
 case datepart(hour,max([ATTTIME])) when 17 then convert(varchar(8),max([ATTTIME]),108) else null end as aftertime
from
 [tb]
group by
 userid,convert(varchar(10),[ATTTIME],120)
order by
 userid----------------结果----------------------------
/* userid      date       montime  aftertime
----------- ---------- -------- ---------
1           2010-01-01 08:10:00 NULL
1           2010-01-02 08:05:00 17:00:00
2           2010-01-01 NULL     17:10:00(3 行受影响)
*/

Answer 3

--只有一记录的怎么取？
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1
select userid,
convert(varchar(10),[ATTTIME],120) as date,
case when min(convert(varchar(8),[ATTTIME],108)) >= '12:00:00' then null else min(convert(varchar(8),[ATTTIME],108)) end as montime,
case when max(convert(varchar(8),[ATTTIME],108)) <= '12:00:00' then null else max(convert(varchar(8),[ATTTIME],108)) end as aftertime
from [tb]
group by userid,convert(varchar(10),[ATTTIME],120)
order by userid----------------------------------
1 2010-01-01 08:10:00 NULL
1 2010-01-02 08:05:00 17:00:00
2 2010-01-01 NULL 17:10:00

Answer 4

--借楼上数据一用
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ATTTIME] datetime,[UserID] int)
insert [tb]
select 1,'2010-01-01 8:10:00',1 union all
select 2,'2010-01-01 8:15:00',1 union all
select 3,'2010-01-01 17:10:00',2 union all
select 4,'2010-01-02 8:05:00',1 union all
select 5,'2010-01-02 17:00:00',1
SELECT 
UserId,[Date],
(SELECT TOP 1 RIGHT(CONVERT(VARCHAR(19),[ATTTIME],120),8) FROM tb WHERE UserID=A.UserID AND DATEPART(HH,[ATTTIME])=8 AND DATEDIFF(DAY,[ATTTIME],A.[Date])=0 ORDER BY [ATTTIME])montime,
(SELECT TOP 1 RIGHT(CONVERT(VARCHAR(19),[ATTTIME],120),8) FROM tb WHERE UserID=A.UserID AND DATEPART(HH,[ATTTIME])=17 AND DATEDIFF(DAY,[ATTTIME],A.[Date])=0 ORDER BY [ATTTIME])aftertimeFROM
(
SELECT DISTINCT UserID,CONVERT(VARCHAR(10),[ATTTIME],120)[Date] FROM tb
)A /*
UserId      Date       montime          aftertime        
----------- ---------- ---------------- ---------------- 
1           2010-01-01 08:10:00         NULL
1           2010-01-02 08:05:00         17:00:00
2           2010-01-01 NULL             17:10:00（所影响的行数为 3 行）
*/

Answer 5

declare @t table(id int identity(1,1),ATTTIME datetime,userid int)
insert @t(ATTTIME,userid) select  '2010-01-01 8:10:00',1
union all select '2010-01-01 8:15:00',1
union all select '2010-01-01 17:10:00',2
union all select '2010-01-02 8:05:00',1
union all select '2010-01-02 17:00:00',1--示例数据
select * FROM @T--查询
select userid
,convert(char(10),atttime,120) as date
,montime=min(case when  datepart(hour,atttime)<=12 then convert(char(8),atttime,108) else null end)
,aftertime=max(case  when datepart(hour,atttime)>12 then convert(char(8),atttime,108) else null end)
from @t
group by userid,convert(char(10),atttime,120)
order by userid
/*(5 行受影响)
id          ATTTIME                 userid
----------- ----------------------- -----------
1           2010-01-01 08:10:00.000 1
2           2010-01-01 08:15:00.000 1
3           2010-01-01 17:10:00.000 2
4           2010-01-02 08:05:00.000 1
5           2010-01-02 17:00:00.000 1(5 行受影响)userid      date       montime  aftertime
----------- ---------- -------- ---------
1           2010-01-01 08:10:00 NULL
1           2010-01-02 08:05:00 17:00:00
2           2010-01-01 NULL     17:10:00
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/

Answer 6

考勤肯定不是三两条SQL指令就解决问题的，呵