时间戳

首页 > 解决方案 > SQL Server根据匹配不同行的两列创建序列

sql - SQL Server根据匹配不同行的两列创建序列

问题描述

我想通过在 SQL 中将一行的结尾与另一行的开头进行匹配来创建一个网络，并根据匹配点创建一个序列。

例如，我有如下数据：

ID    Route   Direction       SLAT       SLON         ELAT    ELON
----------------------------------------------------------------------
1     RT-3          E          40.1        -74.1        40.2    -74.2 
2     RT-3          E          40.2        -74.2        40.3    -74.3 
3     RT-3          E          40.6        -74.6        40.7    -74.7 
4     RT-3          E          40.3        -74.3        40.4    -74.4 
5     RT-3          E          40.5        -74.5        40.6    -74.6 
6     RT-3          E          40.4        -74.4        40.5    -74.5 
7     RT-9          E          40.11       -74.11       40.21   -74.21
8     RT-9          E          40.31       -74.31       40.41   -74.41
9     RT-9          E          40.61       -74.61       40.71   -74.71
10    RT-9          E          40.51       -74.51       40.61   -74.61
11    RT-9          E          40.41       -74.41       40.51   -74.51
12    RT-9          E          40.21       -74.21       40.31   -74.31

所需的结果是

ID      Route   Direction   SLAT        SLON        ELAT    ELON         Seq
-------------------------------------------------------------------------------
1      RT-3        E           40.1        -74.1        40.2    -74.2        1
2      RT-3        E           40.3        -74.3        40.4    -74.4        3
3      RT-3        E           40.2        -74.2        40.3    -74.3        2
4      RT-3        E           40.6        -74.6        40.7    -74.7        6
5      RT-3        E           40.5        -74.5        40.6    -74.6        5
6      RT-3        E           40.4        -74.4        40.5    -74.5        4
7      RT-9        E           40.11       -74.11       40.21   -74.21       1 
8      RT-9        E           40.21       -74.21       40.31   -74.31       2 
9      RT-9        E           40.61       -74.61       40.71   -74.71       6 
10     RT-9        E           40.51       -74.51       40.61   -74.61       5 
11     RT-9        E           40.41       -74.41       40.51   -74.51       4 
12     RT-9        E           40.31       -74.31       40.41   -74.41       3

我想出了这个查询，但不确定如何获取序列

SELECT 
    T1.ROAD_NAME, T1.DIRECTION, T1.SLAT, T1.SLON, T1.ELAT, T1.ELON, 
    T2.SLAT AS SLAT_2, T2.SLON AS SLON_2, T2.ELAT AS ELAT_2, T2.ELON AS ELON_2
FROM 
    myTable T1, myTable T2
WHERE 
    T1.ELAT = T2.SLAT 
    AND T1.ELON = T2.SLON 
    AND T1.DIRECTION = T2.DIRECTION

提前致谢。

布里耶什

标签： sqlsql-server

解决方案

使用分区和排序依据

SELECT T1.ROAD_NAME, T1.DIRECTION, T1.SLAT, T1.SLON, T1.ELAT, T1.ELON 
row_number() over(partition by t1.ROAD_NAME order by T1.SLAT) sequence
FROM myTable t1

推荐阅读