SQL 编号 - Row_Number() - 允许重复行号

Question

我正在使用 SQL Server 2008。我在查询中返回了这些数据，看起来非常像按 Day 和 ManualOrder 排序的...

ID   Day  ManualOrder  Lat     Lon    
1    Mon  0            36.55   36.55  
5    Mon  1            55.55   54.44  
3    Mon  2            44.33   44.30  
10   Mon  3            36.55   36.55  
11   Mon  4            36.55   36.55  
6    Mon  5            20.22   22.11  
9    Mon  6            55.55   54.44  
10   Mon  7            88.99   11.22  
77   Sun  0            23.33   11.11  
77   Sun  1            23.33   11.11  

我想要做的是让这些数据按天排序，然后手动排序...但我想要一个行计数器(我们称之为 MapPinNumber)。问题是，一旦再次遇到同一天的相同纬度/经度，我希望重复该行计数器。然后，如果纬度/经度不同，它可以继续计算下一行的下一行计数器。我们必须在最终结果中保持 Day、ManualOrder 排序。

我将在 map 上绘制这些，并且该数字应代表我将按 ManualOrder 顺序绘制的引脚编号。该数据代表驾驶员的路线，并且他可能在一天中多次前往同一纬度/经度。例如，他开车去沃尔玛，然后 CVS，然后再次回到沃尔玛，然后到沃尔格林。我需要的 MapPinNumber 列应该是 1, 2, 1, 3。由于他周一多次去沃尔玛，但这也是他开车的第一个地方，所以它始终是 map 上的 Pin #1。

这是我需要计算的 MapPinNumber 列的结果。我已经尝试了所有我能想到的 ROW_NUMBER 和 RANK 方法，并且快要疯了!我试图避免使用难看的光标。

ID   Day  ManualOrder  Lat     Lon     MapPinNumber
1    Mon  0            36.55   36.55   1
5    Mon  1            55.55   54.44   2
3    Mon  2            44.33   44.30   3
10   Mon  3            36.55   36.55   1
11   Mon  4            36.55   36.55   1
6    Mon  5            20.22   22.11   4
9    Mon  6            55.55   54.44   2
10   Mon  7            88.99   11.22   5
77   Sun  0            23.33   11.11   1
77   Sun  1            23.33   11.11   1

Answer 1

您可以使用聚合函数 MIN 和 OVER 来创建排名组，并在其之上使用 DENSE_RANK，如下所示。

简要说明

1. MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) 获取 Day 组合的最小 ManualOrder 、纬度 和经度。

2. DENSE_RANK() 只是将此值设置为 1 的增量值。

<强> SQL Fiddle

示例数据

CREATE TABLE Tbl ([ID] int, [Day] varchar(3), [ManualOrder] int, [Lat] int, [Lon] int);

INSERT INTO Tbl ([ID], [Day], [ManualOrder], [Lat], [Lon])
VALUES
    (1, 'Mon', 0, 36.55, 36.55),
    (5, 'Mon', 1, 55.55, 54.44),
    (3, 'Mon', 2, 44.33, 44.30),
    (10, 'Mon', 3, 36.55, 36.55),
    (11, 'Mon', 4, 36.55, 36.55),
    (6, 'Mon', 5, 20.22, 22.11),
    (9, 'Mon', 6, 55.55, 54.44),
    (10, 'Mon', 7, 88.99, 11.22),
    (77, 'Sun', 0, 23.33, 11.11),
    (77, 'Sun', 1, 23.33, 11.11);

查询

;WITH CTE AS 
(
SELECT *,GRP = MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon) FROM Tbl
)
SELECT ID,Day,ManualOrder,Lat,Lon,DENSE_RANK()OVER(PARTITION BY Day ORDER BY GRP) AS RN
FROM CTE
ORDER BY Day,ManualOrder

输出

ID  Day ManualOrder Lat Lon RN
1   Mon 0   36.55   36.55   1
5   Mon 1   55.55   54.44   2
3   Mon 2   44.33   44.30   3
10  Mon 3   36.55   36.55   1
11  Mon 4   36.55   36.55   1
6   Mon 5   20.22   22.11   4
9   Mon 6   55.55   54.44   2
10  Mon 7   88.99   11.22   5
77  Sun 0   23.33   11.11   1
77  Sun 1   23.33   11.11   1