sqlite - SQLite多次插入选择结果

Question

我有一个包含三个表“ contacts”，“ names”和“ contact_names”的数据库。

create table contacts(id integer, gid integer, sid integer);
create table names(id integer primary key, name text);
create table contact_names(fullname text);

insert into names(name) values ('Eberhard');
insert into names(name) values ('Esche');

insert into contacts values(1, (select id from names where name='Eberhard'), (select id from names where name='Esche'));

现在，我想两次插入“给定名称+姓氏”对，一次是“给定名称+姓氏”，一次是“姓氏+给定名字”。我目前有一个这样的陈述来生成这些名称：

SELECT gTable.name || ' ' || sTable.name AS name1, sTable.name || ' ' || gTable.name AS name2 FROM
(
  SELECT name FROM names WHERE id=2
) AS gTable,
(
  SELECT name FROM names WHERE id=1
) AS sTable;

我无法执行的操作是使用类似以下的命令将这些名称现在插入表“ contact_names”中：

INSERT INTO contact_names VALUES (name1), (name2) WITH
SELECT gTable.name || ' ' || sTable.name AS name1, sTable.name || ' ' || gTable.name AS name2 FROM
(
  SELECT name FROM names WHERE id=2
) AS gTable,
(
  SELECT name FROM names WHERE id=1
) AS sTable;

结果表“ contact_names”应包含两个条目（行）“ Esche Eberhard”和“ Eberhard Esche”。有谁知道如何实现这一目标？当然，我可以执行两次select语句，但是我更愿意一次执行一次select。

BR，宇土

PS：也许解释一下我为什么要这样做很有用。用于“ contact_names”的INSERT语句应由触发器执行，该触发器在从“ contacts”中删除条目时被调用。如果我删除一行

DELETE FROM contacts WHERE id=1;

然后应将该联系人的两个姓名组合插入“ contact_names”（以进一步处理这些姓名）。处理删除的名称后，将定期清除该表。

Answer 1

您有一排带有两列，但是您想要两排带有一列。

通常，这可以通过common table expression和compound query完成：

WITH TwoColumns(a, b) AS (
  SELECT a, b FROM ...       -- the original two-column query
)
INSERT INTO ...(x)
SELECT a FROM TwoColumns
UNION ALL
SELECT b FROM TwoColumns;

在这种情况下，我们不需要那么多子查询，并且可以简化一下：

WITH gs(gName, sName) AS (
    SELECT g.name,
           s.name
    FROM names AS g,
         names AS s
    WHERE g.id = 2
      AND s.id = 1
)
INSERT INTO contact_names(fullname)
SELECT gName || ' ' || sName FROM gs
UNION ALL
SELECT sName || ' ' || gName FROM gs;