主页 / dba / 问题 / 96384
Accepted
JoeNahmias
Asked: 2015-03-27 12:20:28 +0800 CST

如何按类别对开始时间列表进行分组？

  • 772

我有一份名单，当他们开始占用建筑物中的各个房间时。像这样：

IF OBJECT_ID('tempdb..#RoomAssignments') IS NOT NULL
  DROP TABLE #RoomAssignments;
CREATE TABLE #RoomAssignments (
  PersonID smallint,
  FloorNum tinyint,
  Room varchar(5),
  EnterTime datetime
);
INSERT INTO #RoomAssignments (PersonID, FloorNum, Room, EnterTime) VALUES
  (24601, 23, 'S', CONVERT(datetime,'2015-02-27 07:42:03.287', 121)),
  (24601, 16, 'F', CONVERT(datetime,'2015-02-27 11:29:59.497', 121)),
  (24601, 12, 'B', CONVERT(datetime,'2015-03-03 14:38:03.243', 121)),
  (24601, 41, 'E', CONVERT(datetime,'2015-03-03 21:03:05.007', 121)),
  (24601, 12, 'B', CONVERT(datetime,'2015-03-05 10:17:36.773', 121)),
  (24601, 15, 'S', CONVERT(datetime,'2015-03-05 12:19:40.573', 121)),
  (24601, 15, 'P', CONVERT(datetime,'2015-03-06 21:51:20.430', 121)),
  (24601, 15, 'L', CONVERT(datetime,'2015-03-09 10:25:00.127', 121)),
  (24601, 16, 'A', CONVERT(datetime,'2015-03-10 17:19:30.550', 121))
;
SELECT * FROM #RoomAssignments
ORDER BY Personid, EnterTime;
/********* #RoomAssignments: ********************
Person FloorNum  Room  EnterTime
------ --------- ----- ------------------------
24601  23        S     2015-02-27 07:42:03.287
24601  16        F     2015-02-27 11:29:59.497
24601  12        B     2015-03-03 14:38:03.243
24601  41        E     2015-03-03 21:03:05.007
24601  12        B     2015-03-05 10:17:36.773
24601  15        S     2015-03-05 12:19:40.573
24601  15        P     2015-03-06 21:51:20.430
24601  15        L     2015-03-09 10:25:00.127
24601  16        A     2015-03-10 17:19:30.550
************************************************/

我想获取按人/楼层分组的数据，下一个开始时间戳成为上一行的结束时间戳。像这样：

/********* Desired Output: ******************************************
id     FloorNum  FromTime                 ToTime
------ --------- ------------------------ ------------------------
24601  23        2015-02-27 07:42:03.287  2015-02-27 11:29:59.497
24601  16        2015-02-27 11:29:59.497  2015-03-03 14:38:03.243
24601  12        2015-03-03 14:38:03.243  2015-03-03 21:03:05.007
24601  41        2015-03-03 21:03:05.007  2015-03-05 10:17:36.773
24601  12        2015-03-05 10:17:36.773  2015-03-05 12:19:40.573
24601  15        2015-03-05 12:19:40.573  2015-03-10 17:19:30.550
24601  16        2015-03-10 17:19:30.550  NULL
********************************************************************/

我试图想出各种窗口函数的用途来以基于集合的方式解决这个问题，但我似乎无法找到一种方法将同一楼层的连续条目分组在一起，同时将非连续的条目分开。

t-sql sql-server-2012

1 个回答

  1. Best Answer

    使用新添加的LAG()和LEAD()功能使其看起来很简单：

    WITH a AS
    ( SELECT PersonID, FloorNum, EnterTime,
             PrevFloorNum = LAG(FloorNum) OVER
                                (PARTITION BY PersonID ORDER BY EnterTime)
      FROM RoomAssignments
    ) 
SELECT PersonID, FloorNum,
       FromTime = EnterTime,
       ToTime = LEAD(EnterTime) OVER 
                    (PARTITION BY PersonID ORDER BY EnterTime)
FROM a
WHERE PrevFloorNum <> FloorNum OR PrevFloorNum IS NULL 
ORDER BY PersonID, FromTime ;

    在SQLfiddle中测试

    • 3

相关问题