sql - 从可用行“分配”的联接

Question

我有一个表X，我想根据另一个表Y中的条目进行更新。它们之间的联接不是唯一的。但是，我希望Y中的每个条目更新X中的一个不同条目。

因此，如果我有表X：

i (unique)  k           v         
----------  ----------  ----------
p           100         b         
q           101         a         
r           202         x         
s           301         a         

表Y：

k (unique)  v         
----------  ----------
0           a         
1           b         
2           a         
3           c         
4           a         

我想以表X结尾：

i           k           v
----------  ----------  ----------
p           1           b
q           0           a
r           202         x
s           2           a

此处的重要结果是，X中v ='a'的两行已从Y更新为k的两个不同值（无关紧要的是哪一个）。

当前，此结果是通过一个额外的列和一个大致类似的程序实现的：

UPDATE X SET X.used = FALSE;
for Yk, Yv in Y:
    UPDATE X
       SET X.k = Yk,
           X.used = TRUE
     WHERE X.i IN (SELECT X.i FROM X
                    WHERE X.v = Yv AND NOT X.used
                    LIMIT 1);

换句话说，通过“用完” Y中的行来实现区别。这不能很好地扩展。

（我使用的是SQLite3和Python，但不要以此为限。）

Answer 1

这可以通过使用rowid配对联接结果来解决。窗口功能不是必需的。 （感谢xQbert将我指向这个方向。）

首先，我们用v对两个表进行排序，以使行标识符按适合连接的顺序创建表。

CREATE TEMPORARY TABLE Xv AS SELECT * FROM X ORDER BY v;
CREATE TEMPORARY TABLE Yv AS SELECT * FROM Y ORDER BY v;

然后，我们可以为每个 v值选择最小的rowid，以便为该值创建一个“ zip联接”，将这些行配对。

SELECT i, Yv.k, Xv.v
  FROM Xv JOIN Yv USING (v)
       JOIN (SELECT v, min(Xv.rowid) AS r FROM Xv GROUP BY v) AS xmin USING (v)
       JOIN (SELECT v, min(Yv.rowid) AS r FROM Yv GROUP BY v) AS ymin
         ON ymin.v = Xv.v AND Xv.rowid - xmin.r = Yv.rowid - ymin.r;

子句 Xv.rowid - min.x = Yv.rowid - min.y是诀窍：它对具有相同 v值的行进行成对匹配，本质上将一个分配给另一个。结果：

i           k           v         
----------  ----------  ----------
q           0           a         
s           2           a         
p           1           b         

然后，在UPDATE中使用此查询的结果很简单。

WITH changes AS (<the SELECT above>)
   UPDATE X SET k = (SELECT k FROM changes WHERE i = X.i)
    WHERE i IN (SELECT i FROM changes);

临时表可以限制为 v的公用值，如果查询很大，则可以在 v上建立索引。

我欢迎改进（或错误！）