sql考勤查询
ID CODE NAME TIME
1 A1 张三 2012-1-1 09:00:12
2 A1 张三 2012-1-1 12:59:11
3 A1 张三 2012-1-1 18:00:23
4 A2 李四 2012-1-1 08:56:32
5 A2 李四 2012-1-1 10:59:12
6 A2 李四 2012-1-1 12:00:08
7 A2 李四 2012-1-1 18:12:23
查询显示每天最早和最晚的记录
结果
1 A1 张三 2012-1-1 09:00
3 A1 张三 2012-1-1 18:00
4 A2 李四 2012-1-1 08:56
6 A2 李四 2012-1-1 18:12
[解决办法]
----------------------------
--?Author??:DBA_Huangzj(發糞塗牆)
--?Date????:2013-09-10?10:43:11
--?Version:
--??????Microsoft?SQL?Server?2014?(CTP1)?-?11.0.9120.5?(X64)?
--??? Jun?10?2013?20:09:10?
--??? Copyright?(c)?Microsoft?Corporation
--??? Enterprise?Evaluation?Edition?(64-bit)?on?Windows?NT?6.2?<X64>?(Build?9200:?)?(Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[CODE] varchar(2),[NAME] varchar(4),[TIME] datetime)
insert [huang]
select 1,'A1','张三','2012-1-1 09:00:12' union all
select 2,'A1','张三','2012-1-1 12:59:11' union all
select 3,'A1','张三','2012-1-1 18:00:23' union all
select 4,'A2','李四','2012-1-1 08:56:32' union all
select 5,'A2','李四','2012-1-1 10:59:12' union all
select 6,'A2','李四','2012-1-1 12:00:08' union all
select 7,'A2','李四','2012-1-1 18:12:23' union all
select 8,'A1','张三','2012-2-1 09:00:12' union all
select 9,'A1','张三','2012-2-1 12:59:11' union all
select 10,'A1','张三','2012-2-1 18:00:23' union all
select 11,'A2','李四','2012-2-1 08:56:32' union all
select 12,'A2','李四','2012-2-1 10:59:12' union all
select 13,'A2','李四','2012-2-1 12:00:08' union all
select 14,'A2','李四','2012-2-1 18:12:23'
--------------开始查询--------------------------
SELECT?*
FROM?[huang]?a
WHERE?EXISTS?(SELECT?1?FROM?(
select?code,name,CONVERT(VARCHAR(10),[time],23)[date],MAX(time)[TIME]
from?[huang]
GROUP?BY?code,name,CONVERT(VARCHAR(10),[time],23)
UNION?ALL?
select?code,name,CONVERT(VARCHAR(10),[time],23)[date],MIN(time)[TIME]
from?[huang]
GROUP?BY?code,name,CONVERT(VARCHAR(10),[time],23)
)b?WHERE?a.code=b.code?AND?a.name=b.name?AND?a.[time]=b.[TIME])
ORDER?BY?ID
----------------结果----------------------------
/*?
ID CODE NAME TIME
----------- ---- ---- -----------------------
1 A1 张三 2012-01-01 09:00:12.000
3 A1 张三 2012-01-01 18:00:23.000
4 A2 李四 2012-01-01 08:56:32.000
7 A2 李四 2012-01-01 18:12:23.000
8 A1 张三 2012-02-01 09:00:12.000
10 A1 张三 2012-02-01 18:00:23.000
11 A2 李四 2012-02-01 08:56:32.000
14 A2 李四 2012-02-01 18:12:23.000
*/
if object_id('#tb') is not null drop table #tb
go
create table #tb([ID] int,[CODE] varchar(2),[NAME] varchar(4),[TIME] datetime)
insert #tb
select 1,'A1','张三','2012-1-1 09:00:12' union all
select 2,'A1','张三','2012-1-1 12:59:11' union all
select 3,'A1','张三','2012-1-1 18:00:23' union all
select 4,'A2','李四','2012-1-1 08:56:32' union all
select 5,'A2','李四','2012-1-1 10:59:12' union all
select 6,'A2','李四','2012-1-1 12:00:08' union all
select 7,'A2','李四','2012-1-1 18:12:23'
select a.*
from #tb a
inner join
(select code,name,min(time)[TIME] from #tb group by code,name
union
select code,name,max(time)[TIME] from #tb group by code,name
)b
on a.CODE=b.CODE and a.NAME=b.NAME and a.[TIME]=b.[TIME]
/*
IDCODENAMETIME
1A1张三2012-01-01 09:00:12.000
3A1张三2012-01-01 18:00:23.000
4A2李四2012-01-01 08:56:32.000
7A2李四2012-01-01 18:12:23.000
*/