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

我正在使用SQL Server2008。我在查询中返回了此数据，该查询看起来很像是按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

我想做的是按天先按顺序排序此数据，然后选择ManualOrder ...但我想要一个行计数器(我们称其为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

最佳答案

您可以将聚合函数MIN与OVER结合使用来创建排名组，并在此基础上使用DENSE_RANK。

简要说明

MIN(ManualOrder)OVER(PARTITION BY Day,Lat,Lon)获取ManualOrder，Day和Lat的最小Lon。

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