mssql考勤查询语句
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
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 allselect 2,'A1','张三','2012-1-1 12:59:11' union allselect 3,'A1','张三','2012-1-1 18:00:23' union allselect 4,'A2','李四','2012-1-1 08:56:32' union allselect 5,'A2','李四','2012-1-1 10:59:12' union allselect 6,'A2','李四','2012-1-1 12:00:08' union allselect 7,'A2','李四','2012-1-1 18:12:23' union allselect 8,'A1','张三','2012-2-1 09:00:12' union allselect 9,'A1','张三','2012-2-1 12:59:11' union allselect 10,'A1','张三','2012-2-1 18:00:23' union allselect 11,'A2','李四','2012-2-1 08:56:32' union allselect 12,'A2','李四','2012-2-1 10:59:12' union allselect 13,'A2','李四','2012-2-1 12:00:08' union allselect 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 allselect 2,'A1','张三','2012-1-1 12:59:11' union allselect 3,'A1','张三','2012-1-1 18:00:23' union allselect 4,'A2','李四','2012-1-1 08:56:32' union allselect 5,'A2','李四','2012-1-1 10:59:12' union allselect 6,'A2','李四','2012-1-1 12:00:08' union allselect 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] /* ID CODE NAME TIME1 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