把两张桌子粘在一起

Question

让我们考虑两个表:第一:

Id Data
1  asd
2  buu

,第二:乌利希期刊指南:

Id Data
10  ffu
11  fffuuu
10001  asd

我想得到一个4列的表，看起来像这样:

Id1  Data1    Id2 Data2
1     asd      10     fuu  
2     buu      11     fffuuu
-1 [any text]  10001   asd

(如果行数不相等，则使用"-1"作为id)如何做到这一点?

我使用的是sqlite3-3.7.3.

UPD2: 表与表之间没有匹配标准，任意任意的匹配对我来说就足够了。

Answer 1

假设id列是唯一的且不为空，您可以通过以下方式"压缩"您的表:

1. 为对应的每一行创建行号按唯一id (as)排序表时的行位置Polishchuk在他的评论中提到);,
2. 模拟具有2个左外连接的完整外连接。

为了演示，我使用了两个行数不同的表:

CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
INSERT INTO foo VALUES (NULL, 'a');
INSERT INTO foo VALUES (NULL, 'b');
INSERT INTO foo VALUES (NULL, 'c');
INSERT INTO foo VALUES (NULL, 'd');
INSERT INTO foo VALUES (NULL, 'e');
INSERT INTO foo VALUES (NULL, 'f');
INSERT INTO foo VALUES (NULL, 'g');
INSERT INTO foo VALUES (NULL, 'h');
INSERT INTO foo VALUES (NULL, 'i');
INSERT INTO foo VALUES (NULL, 'j');
DELETE FROM foo WHERE data IN ('b', 'd', 'f', 'i');
CREATE TABLE bar (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
INSERT INTO bar VALUES (NULL, 'a');
INSERT INTO bar VALUES (NULL, 'b');
INSERT INTO bar VALUES (NULL, 'c');
INSERT INTO bar VALUES (NULL, 'd');
INSERT INTO bar VALUES (NULL, 'e');
INSERT INTO bar VALUES (NULL, 'f');
INSERT INTO bar VALUES (NULL, 'g');
INSERT INTO bar VALUES (NULL, 'h');
INSERT INTO bar VALUES (NULL, 'i');
INSERT INTO bar VALUES (NULL, 'j');
DELETE FROM bar WHERE data IN ('a', 'b');

为了获得更可读的输出，我然后运行:

.headers on
.mode column

然后执行下面的SQL语句:

SELECT COALESCE(id1, -1) AS id1, data1, 
       COALESCE(id2, -1) as id2, data2 
FROM (
    SELECT ltable.rnum AS rnum, 
           ltable.id AS id1, ltable.data AS data1, 
           rtable.id AS id2, rtable.data AS data2
    FROM
        (SElECT (SELECT COUNT(*) FROM foo 
            WHERE id <= T1.id) rnum, id, data FROM foo T1
        ) ltable
        LEFT OUTER JOIN
        (SElECT (SELECT COUNT(*) FROM bar 
            WHERE id <= T1.id) rnum, id, data FROM bar T1
        ) rtable
        ON ltable.rnum=rtable.rnum
    UNION
    SELECT rtable.rnum AS rnum, 
           ltable.id AS id1, ltable.data AS data1, 
           rtable.id AS id2, rtable.data AS data2
    FROM
        (SElECT (SELECT COUNT(*) FROM bar 
            WHERE id <= T1.id) rnum, id, data FROM bar T1
        ) rtable
        LEFT OUTER JOIN
        (SElECT (SELECT COUNT(*) FROM foo 
            WHERE id <= T1.id) rnum, id, data FROM foo T1
        ) ltable
        ON ltable.rnum=rtable.rnum)
ORDER BY rnum

等于:

id1         data1       id2         data2     
----------  ----------  ----------  ----------
1           a           3           c         
3           c           4           d         
5           e           5           e         
7           g           6           f         
8           h           7           g         
10          j           8           h         
-1                      9           i         
-1                      10          j    

这可以"双向"工作，例如，如果您将两个表(foo和bar)颠倒，您将得到:

id1         data1       id2         data2     
----------  ----------  ----------  ----------
3           c           1           a         
4           d           3           c         
5           e           5           e         
6           f           7           g         
7           g           8           h         
8           h           10          j         
9           i           -1                    
10          j           -1