sql考勤查询

DotNetDever 2013-09-10 10:39:57

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

...全文

110 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

Andy__Huang 2013-09-10

打赏
举报

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]

/*
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
*/

發糞塗牆 2013-09-10

打赏
举报

----------------------------------------------------------------
-- 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
*/

DotNetDever 2013-09-10

打赏
举报

引用楼主 u011960669 的回复:

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

要统计每天的

發糞塗牆 2013-09-10

打赏
举报

----------------------------------------------------------------
-- 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'
--------------开始查询--------------------------
SELECT *
FROM [huang] a
WHERE EXISTS (SELECT 1 FROM (
select code,name,MAX(time)[TIME]
from [huang]
GROUP BY code,name
UNION ALL 
select code,name,MIN(time)[TIME]
from [huang]
GROUP BY code,name)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
*/