lz说:六个时间,那么应该是进入1,离开1,进入2,离开2,进入3,离开3 吧 ? use tempdb; /* create table t1 ( 员工ID int not null, 员工姓名 nvarchar(10) not null, 日期打卡时间 nvarchar(50) not null ); insert into t1(员工ID,员工姓名,日期打卡时间) values (1,'李明','2011-3-1 8:00:00'), (2,'陈红','2011-3-1 8:00:00'), (3,'曾小','2011-3-1 8:00:00'), (4,'黄好','2011-3-1 8:00:00'), (5,'李健','2011-3-1 8:00:00'), (6,'潘小','2011-3-1 8:00:00'), (7,'罗明','2011-3-1 8:00:00'), (8,'张代','2011-3-1 8:00:00'), (1,'李明','2011-3-1 12:00::00'), (2,'陈红','2011-3-1 12:00::00'), (3,'曾小','2011-3-1 12:00::00'), (4,'黄好','2011-3-1 12:00::00'), (5,'李健','2011-3-1 12:00::00'), (6,'潘小','2011-3-1 12:00::00'), (7,'罗明','2011-3-1 12:00::00'), (8,'张代','2011-3-1 12:00::00'), (1,'李明','2011-3-1 13:00:00'), (1,'李明','2011-3-1 14:00::00'), (1,'李明','2011-3-1 15:00:00'), (1,'李明','2011-3-1 17:00::00'); */ select t.员工ID, t.员工姓名, MAX(case when t.sortnum = 1 then t.日期打卡时间 end) as [进入1], MAX(case when t.sortnum = 2 then t.日期打卡时间 end) as [离开1], MAX(case when t.sortnum = 3 then t.日期打卡时间 end) as [进入2], MAX(case when t.sortnum = 4 then t.日期打卡时间 end) as [离开2], MAX(case when t.sortnum = 5 then t.日期打卡时间 end) as [进入3], MAX(case when t.sortnum = 6 then t.日期打卡时间 end) as [离开3] from ( select t1.员工ID,t1.员工姓名, CAST(replace(t1.日期打卡时间,'::',':') as datetime) as [日期打卡时间], ROW_NUMBER() over(partition by t1.员工ID,t1.员工姓名 order by CAST(replace(t1.日期打卡时间,'::',':') as datetime)) as [sortnum] from t1 ) as t group by t.员工ID,t.员工姓名 order by t.员工ID;
DECLARE @t TABLE (员工ID INT, 员工姓名 VARCHAR(20), 日期打卡时间 VARCHAR(50)) INSERT @t SELECT 1, '李明', '2011-3-1 8:00:00' UNION ALL SELECT 2, '陈红', '2011-3-1 8:00:00' UNION ALL SELECT 3, '曾小', '2011-3-1 8:00:00' UNION ALL SELECT 4, '黄好', '2011-3-1 8:00:00' UNION ALL SELECT 5, '李健', '2011-3-1 8:00:00' UNION ALL SELECT 6, '潘小', '2011-3-1 8:00:00' UNION ALL SELECT 7, '罗明', '2011-3-1 8:00:00' UNION ALL SELECT 8, '张代', '2011-3-1 8:00:00' UNION ALL SELECT 1, '李明', '2011-3-1 12:00:00' UNION ALL SELECT 2, '陈红', '2011-3-1 12:00:00' UNION ALL SELECT 3, '曾小', '2011-3-1 12:00:00' UNION ALL SELECT 4, '黄好', '2011-3-1 12:00:00' UNION ALL SELECT 5, '李健', '2011-3-1 12:00:00' UNION ALL SELECT 6, '潘小', '2011-3-1 12:00:00' UNION ALL SELECT 7, '罗明', '2011-3-1 12:00:00' UNION ALL SELECT 8, '张代', '2011-3-1 12:00:00'; WITH temp AS ( SELECT 员工ID , 员工姓名 , DATEDIFF(mi, '2010-1-1 00:00:00', 日期打卡时间) AS 日期打卡时间 FROM @t ), temp1 AS ( SELECT TOP 100 * FROM temp PIVOT ( MAX(日期打卡时间) FOR 日期打卡时间 IN ( [611040], [611280] ) ) AS pvt ORDER BY 员工ID ) SELECT 员工ID , 员工姓名 , DATEADD(mi, [611040], '2010-1-1 00:00:00') AS 进入1 , DATEADD(mi, [611280], '2010-1-1 00:00:00') AS 离开1 FROM temp1 员工ID 员工姓名 进入1 离开1 ----------- -------------------- ----------------------- ----------------------- 1 李明 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 2 陈红 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 3 曾小 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 4 黄好 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 5 李健 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 6 潘小 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 7 罗明 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000 8 张代 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000(8 行受影响) 来个2005的,写得不好
use tempdb;
/*
create table t1
(
员工ID int not null,
员工姓名 nvarchar(10) not null,
日期打卡时间 nvarchar(50) not null
);
insert into t1(员工ID,员工姓名,日期打卡时间)
values
(1,'李明','2011-3-1 8:00:00'),
(2,'陈红','2011-3-1 8:00:00'),
(3,'曾小','2011-3-1 8:00:00'),
(4,'黄好','2011-3-1 8:00:00'),
(5,'李健','2011-3-1 8:00:00'),
(6,'潘小','2011-3-1 8:00:00'),
(7,'罗明','2011-3-1 8:00:00'),
(8,'张代','2011-3-1 8:00:00'),
(1,'李明','2011-3-1 12:00::00'),
(2,'陈红','2011-3-1 12:00::00'),
(3,'曾小','2011-3-1 12:00::00'),
(4,'黄好','2011-3-1 12:00::00'),
(5,'李健','2011-3-1 12:00::00'),
(6,'潘小','2011-3-1 12:00::00'),
(7,'罗明','2011-3-1 12:00::00'),
(8,'张代','2011-3-1 12:00::00'),
(1,'李明','2011-3-1 13:00:00'),
(1,'李明','2011-3-1 14:00::00'),
(1,'李明','2011-3-1 15:00:00'),
(1,'李明','2011-3-1 17:00::00');
*/
select
t.员工ID,
t.员工姓名,
MAX(case when t.sortnum = 1 then t.日期打卡时间 end) as [进入1],
MAX(case when t.sortnum = 2 then t.日期打卡时间 end) as [离开1],
MAX(case when t.sortnum = 3 then t.日期打卡时间 end) as [进入2],
MAX(case when t.sortnum = 4 then t.日期打卡时间 end) as [离开2],
MAX(case when t.sortnum = 5 then t.日期打卡时间 end) as [进入3],
MAX(case when t.sortnum = 6 then t.日期打卡时间 end) as [离开3]
from
(
select
t1.员工ID,t1.员工姓名,
CAST(replace(t1.日期打卡时间,'::',':') as datetime) as [日期打卡时间],
ROW_NUMBER() over(partition by t1.员工ID,t1.员工姓名 order by CAST(replace(t1.日期打卡时间,'::',':') as datetime)) as [sortnum]
from t1
) as t
group by t.员工ID,t.员工姓名
order by t.员工ID;
INSERT @t
SELECT 1, '李明', '2011-3-1 8:00:00' UNION ALL
SELECT 2, '陈红', '2011-3-1 8:00:00' UNION ALL
SELECT 3, '曾小', '2011-3-1 8:00:00' UNION ALL
SELECT 4, '黄好', '2011-3-1 8:00:00' UNION ALL
SELECT 5, '李健', '2011-3-1 8:00:00' UNION ALL
SELECT 6, '潘小', '2011-3-1 8:00:00' UNION ALL
SELECT 7, '罗明', '2011-3-1 8:00:00' UNION ALL
SELECT 8, '张代', '2011-3-1 8:00:00' UNION ALL
SELECT 1, '李明', '2011-3-1 12:00:00' UNION ALL
SELECT 2, '陈红', '2011-3-1 12:00:00' UNION ALL
SELECT 3, '曾小', '2011-3-1 12:00:00' UNION ALL
SELECT 4, '黄好', '2011-3-1 12:00:00' UNION ALL
SELECT 5, '李健', '2011-3-1 12:00:00' UNION ALL
SELECT 6, '潘小', '2011-3-1 12:00:00' UNION ALL
SELECT 7, '罗明', '2011-3-1 12:00:00' UNION ALL
SELECT 8, '张代', '2011-3-1 12:00:00';
WITH temp
AS ( SELECT 员工ID ,
员工姓名 ,
DATEDIFF(mi, '2010-1-1 00:00:00', 日期打卡时间) AS 日期打卡时间
FROM @t
),
temp1
AS ( SELECT TOP 100
*
FROM temp PIVOT ( MAX(日期打卡时间) FOR 日期打卡时间 IN ( [611040],
[611280] ) ) AS pvt
ORDER BY 员工ID
)
SELECT 员工ID ,
员工姓名 ,
DATEADD(mi, [611040], '2010-1-1 00:00:00') AS 进入1 ,
DATEADD(mi, [611280], '2010-1-1 00:00:00') AS 离开1
FROM temp1
员工ID 员工姓名 进入1 离开1
----------- -------------------- ----------------------- -----------------------
1 李明 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
2 陈红 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
3 曾小 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
4 黄好 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
5 李健 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
6 潘小 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
7 罗明 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000
8 张代 2011-03-01 08:00:00.000 2011-03-01 12:00:00.000(8 行受影响)
来个2005的,写得不好