CREATE TABLE TB(ID INT, 姓名 NVARCHAR(6), 日期 SMALLDATETIME, 时间 DATETIME)
INSERT TB
SELECT 1, N'张三', '2009-3-2', '2009-3-2 12:30:34' UNION ALL
SELECT 2, N'张三', '2009-3-5', '2009-3-5 14:30:34' UNION ALL
SELECT 3, N'李四', '2009-3-2', '2009-3-2 15:30:34' UNION ALL
SELECT 4, N'李四', '2009-4-1', '2009-4-1 14:30:34' UNION ALL
SELECT 5, N'李四', '2009-4-1', '2009-4-1 16:30:34'
GOSELECT
ID=ROW_NUMBER() OVER (ORDER BY 姓名,日期),
姓名,
日期,
MAX(CASE WHEN SEQ=1 THEN 时间 END) AS 时间1,
MAX(CASE WHEN SEQ=2 THEN 时间 END) AS 时间2
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY 姓名,日期 ORDER BY ID) FROM TB) T
GROUP BY 姓名,日期DROP TABLE TB
/*
ID 姓名 日期 时间1 时间2
-------------------- ------ ----------------------- ----------------------- -----------------------
1 张三 2009-03-02 00:00:00 2009-03-02 12:30:34.000 NULL
2 张三 2009-03-05 00:00:00 2009-03-05 14:30:34.000 NULL
3 李四 2009-03-02 00:00:00 2009-03-02 15:30:34.000 NULL
4 李四 2009-04-01 00:00:00 2009-04-01 14:30:34.000 2009-04-01 16:30:34.000
警告: 聚合或其他 SET 操作消除了空值。
*/
INSERT TB
SELECT 1, N'张三', '2009-3-2', '2009-3-2 12:30:34' UNION ALL
SELECT 2, N'张三', '2009-3-5', '2009-3-5 14:30:34' UNION ALL
SELECT 3, N'李四', '2009-3-2', '2009-3-2 15:30:34' UNION ALL
SELECT 4, N'李四', '2009-4-1', '2009-4-1 14:30:34' UNION ALL
SELECT 5, N'李四', '2009-4-1', '2009-4-1 16:30:34'
GOSELECT
ID=ROW_NUMBER() OVER (ORDER BY 姓名,日期),
姓名,
日期,
MAX(CASE WHEN SEQ=1 THEN 时间 END) AS 时间1,
MAX(CASE WHEN SEQ=2 THEN 时间 END) AS 时间2
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY 姓名,日期 ORDER BY ID) FROM TB) T
GROUP BY 姓名,日期DROP TABLE TB
/*
ID 姓名 日期 时间1 时间2
-------------------- ------ ----------------------- ----------------------- -----------------------
1 张三 2009-03-02 00:00:00 2009-03-02 12:30:34.000 NULL
2 张三 2009-03-05 00:00:00 2009-03-05 14:30:34.000 NULL
3 李四 2009-03-02 00:00:00 2009-03-02 15:30:34.000 NULL
4 李四 2009-04-01 00:00:00 2009-04-01 14:30:34.000 2009-04-01 16:30:34.000
警告: 聚合或其他 SET 操作消除了空值。
*/
-->
MAX(CASE WHEN SEQ>1 THEN 时间 END) AS 时间2