SQLite:具有四个表的完全外部联接 [英] SQLite: full outer join with four tables

问题描述

对此问题的扩展可以找到这里
我想用SQLite联接四个不同的表，它们只有两个共同的列.请看下面的例子

An extension to this question can be found here
I want to join four different tables with SQLite which have just two columns in common. Please take a look at following example

+--------+---+-----+-----+
| table1 |   |     |     |
+--------+---+-----+-----+
| a      | b | lon | lat |
+--------+---+-----+-----+
| 1      | 2 | 111 | 222 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table2 |   |     |     |
+--------+---+-----+-----+
| c      | d | lon | lat |
+--------+---+-----+-----+
| 3      | 4 | 333 | 444 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table3 |   |     |     |
+--------+---+-----+-----+
| e      | f | lon | lat |
+--------+---+-----+-----+
| 5      | 6 | 555 | 666 |
+--------+---+-----+-----+
+--------+---+-----+-----+
| table4 |   |     |     |
+--------+---+-----+-----+
| g      | h | lon | lat |
+--------+---+-----+-----+
| 7      | 8 | 777 | 888 |
+--------+---+-----+-----+

这些表未通过任何外键连接.同样，每行的lon/lat值也不同.最佳输出为:

The tables are not connected by any foreign key. Also, the lon/lat values are different for every row. An optimal output would be:

+------+------+------+------+------+------+------+------+-----+-----+
|  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| None | None | 3    | 4    | None | None | None | NOne | 333 | 444 |
| 1    | 2    | None | None | None | None | None | None | 111 | 222 |
| None | None | None | None | 5    | 6    | None | None | 555 | 666 |
| None | None | None | None | None | None | 7    | 8    | 777 | 888 |
+------+------+------+------+------+------+------+------+-----+-----+

• 再次，这是我的最终目标-表格之间的经/纬度值未互连:
  • b | c | .. | lat | lon |代替
  • 一个| b | c | .. | table1.lat | table2.lat | ...
    • Again, this is my end goal - lat/lon values are not interconnected between the tables:
      • a | b | c | .. | lat | lon | instead of
      • a | b | c | .. | table1.lat | table2.lat | ...

        -- First two tables
        CREATE VIEW ab AS
        SELECT * FROM table1 LEFT JOIN table2 ON ???
        UNION ALL
        SELECT * FROM table2 LEFT JOIN table1 ON ?? WHERE ?? IS NULL
        
        -- 3rd and 4th table
        CREATE VIEW cd AS
        SELECT * FROM table3 LEFT JOIN table4 ON ??
        UNION ALL
        SELECT * FROM table4 LEFT JOIN table3 ON ?? WHERE ?? IS NULL
        
        -- -- JOIN
        SELECT * FROM cd LEFT JOIN ab ON ??
        UNION ALL
        SELECT * FROM cd LEFT JOIN ab ON ?? WHERE ?? IS NULL
        

        推荐答案

        假设您的示例表具有如下数据

        Assuming that your sample tables has data like below

        表1:

        a   b   lon lat
        ---------------
        22  33  11  22
        

        表2:

        c   d   lon lat
        ---------------
        1   2   44  45
        

        表3

        e       f       lon lat
        -----------------------
        NULL    NULL    100 101
        

        表4

        g       h       lon lat
        -----------------------
        NULL    NULL    200 201
        

        ，并且要合并记录，可以使用union all.

        and you want to merge the records, you can use union all.

        select a,b,NULL as c, NULL as d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
        from table1
        union all
        select NULL, NULL,c,d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
        from table2
        union all
        select NULL, NULL,NULL,NULL,e,f, NULL as g, NULL as h, lon,lat
        from table3
        union all
        select NULL, NULL,NULL,NULL,NULL,NULL,g,h, lon,lat
        from table4
        

        结果:

        +------+------+------+------+------+------+------+------+-----+-----+
        |  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
        +------+------+------+------+------+------+------+------+-----+-----+
        | 22   | 33   | NULL | NULL | NULL | NULL | NULL | NULL |  11 |  22 |
        | NULL | NULL | 1    | 2    | NULL | NULL | NULL | NULL |  44 |  45 |
        | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 100 | 101 |
        | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 200 | 201 |
        +------+------+------+------+------+------+------+------+-----+-----+
        

        演示

        DEMO

        这篇关于SQLite:具有四个表的完全外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！