22,206
社区成员
发帖
与我相关
我的任务
分享
--借楼上数据一用
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])aftertime
FROM
(
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 行)
*/
--只有一记录的怎么取?
--> 测试数据:[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
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 行受影响)
*/
----------------------------------------------------------------
-- 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 行受影响)
*/
--还发两个?
--> 测试数据:[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 * 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