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）。问题在于，我希望遇到相同的Lat / Lon和相同的日期后，该行计数器会重复。如果是不同的lat / lon，则可以继续下一行计数器。在最终结果中，我们必须保持Day、ManualOrder的顺序。

我将在地图上绘制这些数据，而这个数字应该代表我将按手动顺序绘制的引脚编号。这些数据表示司机的路线，他可能在一天中多次前往相同的lat / lon。例如，他先开车去沃尔玛，然后去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

- Andy

---

你应该使用RANK()或者DENSE_RANK()（根据情况而定）来代替ROW_NUMBER()。 - Pradeep Kumar

我已经尝试过这些方法，但似乎无法得到正确的结果。 - Andy

我无法理解您的要求，请您具体说明您当前的结果和期望的结果。 - Ravi

你上面展示的MapPinNumber是你想要的预期结果吗？ - Pradeep Kumar

是的。我正在尝试根据其他列计算该列。最终结果必须按Day ASC，ManualOrder ASC排序。 - Andy

这个MapPinNumber需要按照特定顺序排列，不能有间隔吗？否则，您可以通过Lat/Lon计算出一种哈希。 - Stefan Steinegger

Answer 1

这是我使用ROW_NUMBER的尝试： SQL Fiddle

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

- Felix Pamittan

Answer 2

您可以使用带有OVER的聚合函数MIN来创建您的排名分组，然后在其上使用DENSE_RANK进行操作，如下所示。

简要解释：

1. MIN（ManualOrder）OVER（PARTITION BY Day，Lat，Lon）获取Day，Lat和Lon 的组合中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

- ughai

---

非常感谢您。您的答案详细并解释了它，您提供了一个简短而简单的解决方案。您为我节省了很多压力！！它完美地运行。 - Andy

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 Fiddle

- APH

Answer 4

这将给你所需的结果。然而，MapPinNumber 可能不会按照你的结果中显示的顺序完全相同。

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

- Pradeep Kumar

---

如果顺序不同，那么它显然不适用于提问者所述的目的（绘制路径）。 - user743382