SQLite：具有四个包含30多个列的表的完全外部联接

一尘不染

SQLite：具有四个包含30多个列的表的完全外部联接

sql

这个问题是对这篇文章的扩展，我想用SQLite联接四个不同的表，它们只有两个共同的列。但是，假设有30列以上，即不只是 a-h 列。请看下面的例子

表格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

当前的解决方案如下

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 |
+------+------+------+------+------+------+------+------+-----+-----+

问题：如果我不仅有直到h的a列，而且还有直到z的列，即table1，table2，table3和table4中的许多列，该怎么办？我必须在我的sql语句中到处写NULL作为[letter]
@zarruq是一个巨大的帮助，他建议在那种情况下，我可以只使用“ UNION ALL”然后使用“ PIVOT”将列转换为行
但是，我不确定该怎么做。而且，我不知道他100％的意思。
编辑：SQLite不支持数据透视：其他建议吗？

阅读 117

2021-05-16

共1个答案

一尘不染

我不相信当您拥有30列以上的列时，没有特别整齐的方法可以做到这一点。下面是我能做的最好的事情，使用嵌套的CTE来实现完整的外部联接，然后使用coalesce来选择第一个非null纬度/经度。

仍然需要枚举topSELECT语句中的所有30+个字段，但是至少NULL AS ...不需要大量的：

SELECT 
  a, b, c, d, e, f, g, h,
  coalesce(lat1, lat2, lat3, lat4) AS lat,
  coalesce(lon1, lon2, lon3, lon4) AS lon
  FROM (
    WITH t1_x_t2 AS (
        SELECT t1.*, t2.*, 
        t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
        FROM table1 t1 LEFT OUTER JOIN table2 t2 ON 0
      UNION ALL
        SELECT t1.*, t2.*, 
        t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
        FROM table2 t2 LEFT OUTER JOIN table1 t1 ON 0
    ), t3_x_t4 AS (
        SELECT t3.*, t4.*, 
        t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
        FROM table3 t3 LEFT OUTER JOIN table4 t4 ON 0
      UNION ALL
        SELECT t3.*, t4.*, 
        t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
        FROM table4 t4 LEFT OUTER JOIN table3 t3 ON 0
    )
    SELECT t1_x_t2.*, t3_x_t4.* FROM t1_x_t2 LEFT OUTER JOIN t3_x_t4 ON 0
    UNION ALL
    SELECT t1_x_t2.*, t3_x_t4.* FROM t3_x_t4 LEFT OUTER JOIN t1_x_t2 ON 0
)

2021-05-16