在连续线段上连接两个表

Question

我想连接两个表，其中第一个表的条目比第二个多，这样每个表中的行就可以按顺序连接。也许一个小例子会有所帮助：

表T:

| tid | sid | ron | val | seqno |
| --- | --- | --- | --- | ---   |
| 1   | a   | x1  | 15  | 1     |
| 2   | b   | x2  | 10  | 3     |
| 2   | b   | x3  | 20  | 4     |
| 3   | a   | x5  | 10  | 5     |
| 4   | c   | x9  | 15  | 7     |
| 4   | c   | x9  | 15  | 8     |
| 4   | c   | x9  | 20  | 10    |
| 4   | c   | x9  | 15  | 11    |
| 6   | b   | x11 | 22  | 12    |
| 7   | b   | x12 | 10  | 14    |
| 7   | b   | x13 | 10  | 16    |
| 7   | b   | x13 | 10  | 17    |
| 7   | b   | x14 | 10  | 19    |

第二个表(表C(如下(实际上是更多的列(：

| tid | sid | ron | val | fid |
| --- | --- | --- | --- | --- |
| 2   | b   | x3  | 20  | 54  |
| 4   | c   | x9  | 15  | 12  |
| 4   | c   | x9  | 15  | 14  |
| 4   | c   | x9  | 20  | 15  |
| 4   | c   | x9  | 15  | 20  |
| 7   | b   | x13 | 10  | 112 |
| 7   | b   | x13 | 10  | 113 |

每个表中都有seqNo和fid，用于在(tid, sid, ron)形成的组中提供排序，这就是我想要维护的排序。

我怎样才能从这两张桌子转到下一张桌子？

| tid | sid | ron | val | fid | seqno |
| --- | --- | --- | --- | --- | ---   | 
| 2   | b   | x3  | 20  | 54  | 4     |
| 4   | c   | x9  | 15  | 12  | 7     |
| 4   | c   | x9  | 15  | 14  | 8     |
| 4   | c   | x9  | 20  | 15  | 10    |
| 4   | c   | x9  | 15  | 20  | 11    |
| 7   | b   | x13 | 10  | 112 | 16    |
| 7   | b   | x13 | 10  | 113 | 17    |

我不能为组中的每个元素分配一个秩，并将其用于LEFT JOIN内部的匹配，因为有些情况下匹配不是从组的末尾开始的(例如tid=7(。此外，由于同一组中的val可能有重复的值，我也不能盲目匹配，因为这可能会增加行数。

Answer 1

这是我昨晚设法弄到很晚的东西，似乎工作正常：

WITH
table_t AS (
SELECT *
FROM (VALUES
(1,'a','x1',15,1),
(2,'b','x2',10,3),
(2,'b','x3',20,4),
(3,'a','x5',10,5),
(4,'c','x9',15,7),
(4,'c','x9',15,8),
(4,'c','x9',20,10),
(4,'c','x9',15,11),
(6,'b','x11',22,12),
(7,'b','x12',10,14),
(7,'b','x13',10,16),
(7,'b','x13',10,17),
(7,'b','x14',10,19)
) AS c(tid, sid, ron, val, seq)
),
table_t_ranked AS (
SELECT *
, DENSE_RANK() OVER (PARTITION BY tid, sid, ron ORDER BY seq ASC) AS ranking
FROM table_t
),
table_c AS (
SELECT *
FROM (VALUES
(2,'b','x3',20,54),
(4,'c','x9',15,12),
(4,'c','x9',15,14),
(4,'c','x9',20,15),
(4,'c','x9',15,20),
(7,'b','x13',10,112),
(7,'b','x13',10,113)
) AS c(tid, sid, ron, val, fid)
),
table_c_ranked AS (
SELECT *
, DENSE_RANK() OVER (PARTITION BY tid, sid, ron ORDER BY fid ASC) AS ranking
FROM table_c
),
foo AS (
SELECT c.*
, t.seq
, t.ranking as ranking_t
FROM table_c_ranked c
LEFT JOIN table_t_ranked t
ON c.tid = t.tid
AND c.sid = t.sid
AND c.ron = t.ron
AND c.val = t.val
)
SELECT tid, sid, ron, val, fid, seq
FROM foo       
WHERE ranking = ranking_t
ORDER BY tid, seq