SQL Number语言 - Row_Number() -允许重复行号

Question

我正在使用SQL Server 2008。我在一个查询中返回了这个数据，这个查询看起来很像这样按日期和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订购。

我将在地图上绘制这些，这个数字应该代表我将在ManualOrder顺序中绘制的引脚编号。这个数据代表了一个司机的路线，他可能会在一天的行程中多次去同一个纬度/纬度。例如，他开车去沃尔玛，然后去CVS，然后再回到沃尔玛，然后去沃尔格林。我需要的MapPinNumber列应该是1,2,1,3。因为他周一去了好几次沃尔玛，但那也是他开车去的第一个地方，所以它总是在地图上的Pin #1。

下面是我需要计算的MapPinNumber列的结果。对于ROW_NUMBER和RANK，我已经尝试了所有我能想到的方法，但还是疯了!我试图避免使用难看的CURSOR。

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、Lat和Lon组合的最小ManualOrder。

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

SQL小提琴

示例数据

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

Answer 2

这是我使用ROW_NUMBER的尝试:

SQL小提琴

WITH CteRN AS(
    SELECT *,
        Rn = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder),
        Grp = ROW_NUMBER() OVER(PARTITION BY Day, Lat, Lon ORDER BY ManualOrder)
    FROM tbl
),
CteBase AS(
    SELECT *,
        N = ROW_NUMBER() OVER(PARTITION BY Day ORDER BY ManualOrder)
    FROM CteRN
    WHERE Grp = 1
)
SELECT 
    r.ID, r.Day, r.ManualOrder, r.Lat, r.Lon,
    MapPinNumber = ISNULL(b.N, r.RN)
FROM CteRN r
LEFT JOIN CteBase b
    ON b.Day = r.Day
    AND b.Lat = r.Lat
    AND b.Lon = r.Lon
ORDER BY 
    r.Day, r.ManualOrder

Answer 3

这可能不是最优雅的解决方案，但它是有效的:

Select a.*, b.MapPinOrder from MyTable a
left join
  (
  select distinct Day, Lat, Lon
    , row_number() 
      over (partition by Day order by min(ManualOrder)) as MapPinOrder
  from MyTable
  group by Day, Lat, Lon
  ) b
on a.day = b.day 
  and a.lat = b.lat 
  and a.lon = b.lon  

使用所需的顺序分别计算行，然后将它们连接回完整表。

SQL小提琴

Answer 4

这将给出您需要的结果。然而，MapPinNumber可能与结果中显示的顺序不完全相同。

SELECT *, 
    MapPinNumber = DENSE_RANK() OVER (PARTITION BY Day ORDER BY Lat, Lon) 
FROM Table1
ORDER BY Day, ManualOrder