╔════════════════════╦═══════════╦══════════╦═══════╦══════╗
║      ENDTIME       ║ TRAILERID ║ POSITION ║ STORE ║ STOP ║
╠════════════════════╬═══════════╬══════════╬═══════╬══════╣
║ 11/7/2017 15:15:44 ║       145 ║ L1       ║ BOS   ║    1 ║
║ 11/7/2017 15:16:57 ║       145 ║ L2       ║ NY    ║    3 ║
║ 11/7/2017 15:17:24 ║       145 ║ R1       ║ NY    ║    2 ║
║ 11/7/2017 15:17:57 ║       145 ║ R2       ║ NY    ║    1 ║
║ 11/7/2017 15:18:26 ║       145 ║ L3       ║ CT    ║    2 ║
║ 11/7/2017 15:34:24 ║       145 ║ R3       ║ CT    ║    1 ║
║ 11/7/2017 16:02:01 ║       999 ║ L1       ║ NJ    ║    2 ║
║ 11/7/2017 16:22:33 ║       999 ║ R1       ║ NJ    ║    1 ║
║ 11/7/2017 16:23:14 ║       999 ║ L2       ║ WA    ║    3 ║
║ 11/7/2017 16:27:16 ║       999 ║ R2       ║ WA    ║    2 ║
║ 11/7/2017 16:27:46 ║       999 ║ L3       ║ WA    ║    1 ║
╚════════════════════╩═══════════╩══════════╩═══════╩══════╝

我正在尝试将Stop字段更改为如下所示
╔════════════════════╦═══════════╦══════════╦═══════╦══════╗
║      ENDTIME       ║ TRAILERID ║ POSITION ║ STORE ║ STOP ║
╠════════════════════╬═══════════╬══════════╬═══════╬══════╣
║ 11/7/2017 15:15:44 ║       145 ║ L1       ║ BOS   ║    3 ║
║ 11/7/2017 15:16:57 ║       145 ║ L2       ║ NY    ║    2 ║
║ 11/7/2017 15:17:24 ║       145 ║ R1       ║ NY    ║    2 ║
║ 11/7/2017 15:17:57 ║       145 ║ R2       ║ NY    ║    2 ║
║ 11/7/2017 15:18:26 ║       145 ║ L3       ║ CT    ║    1 ║
║ 11/7/2017 15:34:24 ║       145 ║ R3       ║ CT    ║    1 ║
║ 11/7/2017 16:02:01 ║       999 ║ L1       ║ NJ    ║    2 ║
║ 11/7/2017 16:22:33 ║       999 ║ R1       ║ NJ    ║    2 ║
║ 11/7/2017 16:23:14 ║       999 ║ L2       ║ WA    ║    1 ║
║ 11/7/2017 16:27:16 ║       999 ║ R2       ║ WA    ║    1 ║
║ 11/7/2017 16:27:46 ║       999 ║ L3       ║ WA    ║    1 ║
╚════════════════════╩═══════════╩══════════╩═══════╩══════╝

我的尝试是使用row_number和rank,但我无法获得正确的结果。给我第一张桌子的结果。
SELECT ps.ENDTIME, ps.TRUCKID as TRAILERID, ps.CONTAINERPOSITION as POSITION,ps.STORE,RANK() OVER( PARTITION by ps.STORE  ORDER BY ps.ENDTIME DESC)AS STOP
FROM TRAILERDATA ps
ORDER BY ps.ENDTIME ASC

最重要的是,即使ENDTIME的顺序不正确,也要按具有相同等级的一组商店进行标签。

最佳答案

样本数据

DECLARE @T TABLE
    (EndTime datetime2(0), TrailerID int, Position varchar(50), Store varchar(50));
INSERT INTO @T (EndTime, TrailerID, Position, Store) VALUES
('2017-11-07 15:15:44',145,'L1','BOS'),
('2017-11-07 15:16:57',145,'L2','NY'),
('2017-11-07 15:17:24',145,'R1','NY'),
('2017-11-07 15:17:57',145,'R2','NY'),
('2017-11-07 15:18:26',145,'L3','CT'),
('2017-11-07 15:34:24',145,'R3','CT'),
('2017-11-07 16:02:01',999,'L1','NJ'),
('2017-11-07 16:22:33',999,'R1','NJ'),
('2017-11-07 16:23:14',999,'L2','WA'),
('2017-11-07 16:27:16',999,'R2','WA'),
('2017-11-07 16:27:46',999,'L3','WA');

查询
WITH
CTE
AS
(
    SELECT
        EndTime, TrailerID, Position, Store
        ,MAX(EndTime) OVER (PARTITION BY TrailerID, Store) AS SortingTime
    FROM @T
)
SELECT
    EndTime, TrailerID, Position, Store
    ,SortingTime
    ,DENSE_RANK() OVER (PARTITION BY TrailerID ORDER BY SortingTime DESC) AS Stop
FROM CTE
ORDER BY TrailerID, Stop DESC, EndTime;

结果
+---------------------+-----------+----------+-------+---------------------+------+
|       EndTime       | TrailerID | Position | Store |     SortingTime     | Stop |
+---------------------+-----------+----------+-------+---------------------+------+
| 2017-11-07 15:15:44 |       145 | L1       | BOS   | 2017-11-07 15:15:44 |    3 |
| 2017-11-07 15:16:57 |       145 | L2       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:17:24 |       145 | R1       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:17:57 |       145 | R2       | NY    | 2017-11-07 15:17:57 |    2 |
| 2017-11-07 15:18:26 |       145 | L3       | CT    | 2017-11-07 15:34:24 |    1 |
| 2017-11-07 15:34:24 |       145 | R3       | CT    | 2017-11-07 15:34:24 |    1 |
| 2017-11-07 16:02:01 |       999 | L1       | NJ    | 2017-11-07 16:22:33 |    2 |
| 2017-11-07 16:22:33 |       999 | R1       | NJ    | 2017-11-07 16:22:33 |    2 |
| 2017-11-07 16:23:14 |       999 | L2       | WA    | 2017-11-07 16:27:46 |    1 |
| 2017-11-07 16:27:16 |       999 | R2       | WA    | 2017-11-07 16:27:46 |    1 |
| 2017-11-07 16:27:46 |       999 | L3       | WA    | 2017-11-07 16:27:46 |    1 |
+---------------------+-----------+----------+-------+---------------------+------+

在结果中,您可以看到SortingTime如何允许计算所需的DENSE_RANK。本质上,SortingTime定义了按TrailerIDStore进行分组的方法,还方便地提供了一种进行分类的方式。

上面的查询依赖于EndTime对于不同的Stores是不同的。更具体地说,最大时间不同。如果您有两个Stores具有相同的最大EndTime,则查询将为其分配相同的Stop

如果有这种可能,则将Store添加到ORDER BY中:
WITH
CTE
AS
(
    SELECT
        EndTime, TrailerID, Position, Store
        ,MAX(EndTime) OVER (PARTITION BY TrailerID, Store) AS SortingTime
    FROM @T
)
SELECT
    EndTime, TrailerID, Position, Store
    ,SortingTime
    ,DENSE_RANK() OVER (PARTITION BY TrailerID ORDER BY SortingTime DESC, Store) AS Stop
FROM CTE
ORDER BY TrailerID, Stop DESC, EndTime;

关于sql - 基于多个字段对记录进行排名,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47168422/

10-12 18:00
查看更多