在线SQL求助

[昨天]

有一个上班打卡表数把以下

员工ID 员工姓名 日期打卡时间
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
现在想用SQL查询以下效果
员工ID 员工姓名 进入1 离开1 进入2 离开2 进入3 离开3 进入4 离开4
1 李明 2011-3-1 8:00:00 12:00::00
2 陈红 2011-3-1 8:00:00 12:00::00
3 曾小 2011-3-1 8:00:00 12:00::00
4 黄好 2011-3-1 8:00:00 12:00::00
5 李健 2011-3-1 8:00:00 12:00::00
6 潘小 2011-3-1 8:00:00 12:00::00
7 罗明 2011-3-1 8:00:00 12:00::00
8 张代 2011-3-1 8:00:00 12:00::00
数据1日期打卡时间是一列的 总共有六个打卡时间进入1,离开1，进入2,离开2
进入3,离开3，进入4,离开4

[昨天]

日期打卡时间 不是两个列是一个列

[javatemptation]

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;

[qgqch2008]

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的，写得不好

[昨天]

有没有用sql 2000写的。。代码都是2005

[Rich_er]

现在2000的淘汰了, 2005的还跟得上。