sql - 使用SQLite合并数据

Question

我正在尝试找到一种使用cli在sqlite数据库中合并数据的方法。

例如：

带有col1（键）和col2（值1）的表1

╔══════╦═════════╗
║ col1 ║ values1 ║
╠══════╬═════════╣
║ 1234 ║     456 ║
║ 1235 ║     789 ║
╚══════╩═════════╝

带有col1（键）和col2（值2）的表2

╔══════╦═════════╗
║ col1 ║ values2 ║
╠══════╬═════════╣
║ 1233 ║     235 ║
║ 1234 ║     457 ║
║ 1235 ║     790 ║
╚══════╩═════════╝

目标：
带有col1（键）和col2（值1）和col3（值2）的表3

╔══════╦═════════╦═════════╗
║ col1 ║ values1 ║ values2 ║
╠══════╬═════════╬═════════╣
║ 1233 ║ NULL    ║     235 ║
║ 1234 ║ 456     ║     457 ║
║ 1235 ║ 789     ║     790 ║
╚══════╩═════════╩═════════╝

如何在SQL中做到这一点？

Answer 1

您可以使用CREATE TABLE AS：

SqlFiddleDemo

数据：

CREATE TABLE TABLE1(col1 INT, values1 INT);

INSERT INTO TABLE1
VALUES (1234,456), (1235,789);

CREATE TABLE TABLE2(col1 INT, values2 INT);

INSERT INTO TABLE2
VALUES (1233,235), (1234,457), (1235,790);

主要查询：

CREATE TABLE TABLE3
AS
SELECT col1, MAX(values1) AS values1, MAX(values2) AS values2
FROM(
   SELECT t1.col1 AS col1, t1.values1 AS values1, t2.values2 AS values2
   FROM TABLE1 t1
   LEFT JOIN TABLE2 t2
     ON t1.col1 = t2.col1
   UNION ALL
   SELECT t2.col1 AS col1, t1.values1 AS values1, t2.values2 AS values2
   FROM TABLE2 t2
   LEFT JOIN TABLE1 t1
     ON t1.col1 = t2.col1) as tab
GROUP BY col1;

当SQLite支持 FULL OUTER JOIN时可能会容易得多，那么就不需要使用 UNION ALL进行子查询和 ORACLE了：

SqlFiddleDemo2

CREATE TABLE TABLE3
AS
SELECT 
   COALESCE(t1.col1, t2.col1) AS col1,
   t1.values1 AS values1,
   t2.values2 AS values2
FROM TABLE1 t1
FULL JOIN TABLE2 t2
  ON t1.col1 = t2.col1;

或 SQL SERVER：

SqlFiddleDemo3

SELECT 
    COALESCE(t1.col1, t2.col1) AS col1,
    t1.values1 AS values1,
    t2.values2 AS values2
INTO TABLE3
FROM TABLE1 t1
FULL JOIN TABLE2 t2
  ON t1.col1 = t2.col1;

警告：
创建初始表后，您必须使用以下命令 INSERT：

SqlFiddleDemo4

INSERT INTO TABLE3(col1,values1, values2)
SELECT ...