如何联合SQLite中每个表的所有公共列

Question

对于表
t1:

C1 C2 C3
1  2  3
4  5  6

表t2:

C1 C2 C4
33 44 55

应该编写什么查询来对所有公共列进行联合，从而使结果如下:

C1  C2
1   2
4   5
33  44

重要的是要注意，我不是在寻找一个只有两个表的解决方案，而是在寻找一个解决方案，在数据库中的每个表都这样做。数据库是SQLite

Answer 1

SQLite不支持动态sql，所以你最多只能使用它自己的功能来构造一个sql语句来做你想做的事情，然后你可以使用Java或Python等编程语言来执行它。

这个查询:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT pti.name col
FROM tables t CROSS JOIN pragma_table_info(t.name) pti
GROUP BY col
HAVING COUNT(*) = (SELECT COUNT(*) FROM tables);

返回数据库中所有表中共有的所有列。通过使用GROUP_CONCAT()，您可以将所有这些列作为逗号分隔的列表，您可以在SELECT语句中使用:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(col) columns
FROM (
SELECT pti.name col
FROM tables t CROSS JOIN pragma_table_info(t.name) pti
GROUP BY col
HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
);

最后，将关键字'SELECT'和'FROM'与每个表的名称连接起来，并再次使用GROUP_CONCAT()和'UNION ALL'(或'UNION')作为分隔符构建sql语句:

WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(sql, ' UNION ALL ') sql
FROM ( 
SELECT 'SELECT ' || 
(
SELECT GROUP_CONCAT(col) columns
FROM (
SELECT pti.name col
FROM tables t CROSS JOIN pragma_table_info(t.name) pti
GROUP BY col
HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
)
) || ' FROM ' || name AS sql
FROM tables
);

返回如下字符串:

SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SELECT col1,col2 FROM table3