表结构是这样的:一个简单的考勤表 nameid name time
1 小明 2008-10-01 15:41:25
1 小明 2008-10-01 15:42:25
1 小明 2008-10-01 15:31:25
1 小明 2008-10-02 15:41:25
1 小明 2008-10-02 15:42:25
1 小明 2008-10-02 15:31:25
2 小红 2008-10-01 15:31:25
2 小红 2008-10-01 15:41:25
2 小红 2008-10-01 15:41:25
现在是需要查出每个人的上下班时间,所以取每一天的按照时间先后顺序 取第一条和最后一条记录!
如:
1 小明 2008-10-01 15:31:25 2008-10-01 15:42:25
1 小明 2008-10-02 15:31:25 2008-10-02 15:42:25
1 小明 2008-10-01 15:41:25
1 小明 2008-10-01 15:42:25
1 小明 2008-10-01 15:31:25
1 小明 2008-10-02 15:41:25
1 小明 2008-10-02 15:42:25
1 小明 2008-10-02 15:31:25
2 小红 2008-10-01 15:31:25
2 小红 2008-10-01 15:41:25
2 小红 2008-10-01 15:41:25
现在是需要查出每个人的上下班时间,所以取每一天的按照时间先后顺序 取第一条和最后一条记录!
如:
1 小明 2008-10-01 15:31:25 2008-10-01 15:42:25
1 小明 2008-10-02 15:31:25 2008-10-02 15:42:25
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (nameid INT,name VARCHAR(4),[time] DATETIME)
INSERT INTO #T
SELECT 1,'小明','2008-10-01 15:41:25' UNION ALL
SELECT 1,'小明','2008-10-01 15:42:25' UNION ALL
SELECT 1,'小明','2008-10-01 15:31:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:41:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:42:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:31:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:31:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:41:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:41:25'--SQL查询如下:SELECT name,
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)/*
name start_time end_time
---- ----------------------- -----------------------
小红 2008-10-01 15:31:25.000 2008-10-01 15:41:25.000
小明 2008-10-01 15:31:25.000 2008-10-01 15:42:25.000
小明 2008-10-02 15:31:25.000 2008-10-02 15:42:25.000(3 行受影响)*/
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
CREATE TABLE #T (nameid INT,name VARCHAR(4),[time] DATETIME)
INSERT INTO #T
SELECT 1,'小明','2008-10-01 15:41:25' UNION ALL
SELECT 1,'小明','2008-10-01 15:42:25' UNION ALL
SELECT 1,'小明','2008-10-01 15:31:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:41:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:42:25' UNION ALL
SELECT 1,'小明','2008-10-02 15:31:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:31:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:41:25' UNION ALL
SELECT 2,'小红','2008-10-01 15:41:25'--SQL查询如下:SELECT nameid,name,
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY
nameid,name,CONVERT(VARCHAR(10),[time],120)
/*
nameid name start_time end_time
----------- ---- ----------------------- -----------------------
1 小明 2008-10-01 15:31:25.000 2008-10-01 15:42:25.000
1 小明 2008-10-02 15:31:25.000 2008-10-02 15:42:25.000
2 小红 2008-10-01 15:31:25.000 2008-10-01 15:41:25.000
*/
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
select nameid ,name,上班=min(time),落班=max(time) from tb group by day(time),nameid ,name
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
SELECT name,
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM TABLENAME
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
Set Nocount On
declare @1 table([nameid] int,[name] nvarchar(2),[time] Datetime)
Insert @1
select 1,N'小明','2008-10-01 15:41:25' union all
select 1,N'小明','2008-10-01 15:42:25' union all
select 1,N'小明','2008-10-01 15:31:25' union all
select 1,N'小明','2008-10-02 15:41:25' union all
select 1,N'小明','2008-10-02 15:42:25' union all
select 1,N'小明','2008-10-02 15:31:25' union all
select 2,N'小红','2008-10-01 15:31:25' union all
select 2,N'小红','2008-10-01 15:41:25' union all
select 2,N'小红','2008-10-01 15:41:25'
Select nameid,name,Min(time),Max(time)
From @1
Group by nameid,name,convert(char(8),time,112) /*
nameid name
----------- ---- ----------------------- -----------------------
1 小明 2008-10-01 15:31:25.000 2008-10-01 15:42:25.000
1 小明 2008-10-02 15:31:25.000 2008-10-02 15:42:25.000
2 小红 2008-10-01 15:31:25.000 2008-10-01 15:41:25.000
*/
from table
group by nameid,name,convert(varchar(10),getdate(),120)
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY
name,CONVERT(VARCHAR(10),[time],120)
不难。
if object_Id('T')is not null
drop table T
go
create table T(nameid int,name nvarchar(10),time datetime)
insert into T select 1,'小明','2008-10-01 15:41:25'
union all select 1,'小明','2008-10-01 15:42:25'
union all select 1,'小明','2008-10-01 15:31:25'
union all select 1,'小明','2008-10-02 15:41:25'
union all select 1,'小明','2008-10-02 15:42:25'
union all select 1,'小明','2008-10-02 15:31:25'
union all select 2,'小红','2008-10-01 15:31:25'
union all select 2,'小红','2008-10-01 15:41:25'
union all select 2,'小红','2008-10-01 15:41:25'
--------
select name,min(time) start_time,max(time) end_time from t
group by name,convert(varchar(10),time,120)小红 2008-10-01 15:31:25.000 2008-10-01 15:41:25.000
小明 2008-10-01 15:31:25.000 2008-10-01 15:42:25.000
小明 2008-10-02 15:31:25.000 2008-10-02 15:42:25.000
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
select 1,N'小明','2008-10-01 15:42:25' union
select 1,N'小明','2008-10-01 15:31:25' union
select 1,N'小明','2008-10-02 15:41:25' union
select 1,N'小明','2008-10-02 15:42:25' union
select 1,N'小明','2008-10-02 15:31:25' union
select 2,N'小红','2008-10-01 15:31:25' union
select 2,N'小红','2008-10-01 15:41:25' union
select 2,N'小红','2008-10-01 15:41:25'
select * from #tselect *,
(select max([time]) from #t where [name]=t.[name] and datediff(day,[time],t.[time])=0 )
from #t t
where [time]=(select min([time]) from #t where [name]=t.[name] and datediff(day,[time],t.[time])=0 )
drop table
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM TABLENAME
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
from table
group by nameid,name,day(time)
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM TABLENAME
GROUP BY name,CONVERT(VARCHAR(10),[time],120)
MIN([time]) AS start_time,
MAX([time]) AS end_time
FROM #T
GROUP BY name,CONVERT(VARCHAR(10),[time],120)