sql - 将父级与多个子级连接后合并重复的 SQLite 列

Question

我设置了一个 SQLite 数据库，如下所示:
我目前正在寻找将所有子表加入父表。正如你所看到的，所有的 child 都是以同样的方式设计的，所以我希望他们能很好地融合在一起。
不幸的是，使用左连接连接表的结果是:
使用SQLite，是否可以合并列以便忽略空值，而剩余的starting_item_ids和item_ids都显示在同一列中？
任何帮助将不胜感激。
编辑:这是一个最小的可重复示例

CREATE TABLE `starting_item` (
  `starting_item_id` integer PRIMARY KEY AUTOINCREMENT,
  `quantity` integer
);

CREATE TABLE `starting_weapon` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_armor` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_gear` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);

CREATE TABLE `starting_tool` (
  `starting_item_id` integer,
  `item_id` integer,
  FOREIGN KEY (`starting_item_id`) REFERENCES `starting_item` (`starting_item_id`)
);


INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);
INSERT INTO starting_item (quantity) VALUES (1);


INSERT INTO starting_weapon (starting_item_id, item_id) VALUES (1, 4);
INSERT INTO starting_armor (starting_item_id, item_id) VALUES (2, 7);
INSERT INTO starting_gear (starting_item_id, item_id) VALUES (3, 30);
INSERT INTO starting_tool (starting_item_id, item_id) VALUES (4, 20);


select * from starting_item 
    left join starting_weapon on starting_weapon.starting_item_id = starting_item.starting_item_id
    left join starting_armor on starting_armor.starting_item_id = starting_item.starting_item_id
    left join starting_gear on starting_gear.starting_item_id = starting_item.starting_item_id
    left join starting_tool on starting_tool.starting_item_id = starting_item.starting_item_id

当前结果:

+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
| starting_item_id | quantity | starting_item_id | item_id | starting_item_id | item_id | starting_item_id | item_id | starting_item_id | item_id |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                1 |        1 |                1 |       4 |                  |         |                  |         |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                2 |        1 |                  |         | 2                | 7       |                  |         |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                3 |        1 |                  |         |                  |         | 3                | 30      |                  |         |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+
|                4 |        1 |                  |         |                  |         |                  |         | 4                | 20      |
+------------------+----------+------------------+---------+------------------+---------+------------------+---------+------------------+---------+

预期结果:

+------------------+----------+------------------+---------+
| starting_item_id | quantity | starting_item_id | item_id |
+------------------+----------+------------------+---------+
|                1 |        1 |                1 |       4 |
+------------------+----------+------------------+---------+
|                2 |        1 |                2 |       7 |
+------------------+----------+------------------+---------+
|                3 |        1 |                3 |      30 |
+------------------+----------+------------------+---------+
|                4 |        1 |                4 |      20 |
+------------------+----------+------------------+---------+

Answer 1

您可以使用以下查询获得声明的所需输出(减去帧，但这只是一个配置问题):

select * from starting_item a
join (      select * from starting_weapon
      union select * from starting_armor
      union select * from starting_gear
      union select * from starting_tool) b
on a.starting_item_id=b.starting_item_id;

它使用相同结构的子表的多“联合”，并使用别名“a”和“b”将其与起始项表连接。
结果完全是规定的所需输出:

starting_item_id  quantity    starting_item_id  item_id
----------------  ----------  ----------------  ----------
1                 1           1                 4
2                 1           2                 7
3                 1           3                 30
4                 1           4                 20

但是，在我看来，避免冗余列(ID 的两倍)是可取的。
而且我也觉得添加一个指标(我们看到什么样的项目)的小技巧是有帮助的。所以我提供这个是为了漂亮:

select * from starting_item
join (      select *, "weapon" kind from starting_weapon
      union select *, "armor"  kind from starting_armor
      union select *, "gear"   kind from starting_gear
      union select *, "tool"   kind from starting_tool)
using(starting_item_id);

如果你喜欢它，它会让你:

starting_item_id  quantity    item_id     kind
----------------  ----------  ----------  ----------
1                 1           4           weapon
2                 1           7           armor
3                 1           30          gear
4                 1           20          tool

第一个变化是加入 using()而不是 on ... ，它(尽管使用 * 为方便起见)只为您提供一个 ID 列。
第二个变化是添加一个名为“kind”的列，在每个“联合”部分中明确给出内容。这个“联合”和“连接”很好地结合在一起，为您提供了一个命名的指标列。优点是您可以例如按该指标列排序以保持整洁。
最后，请考虑这里使用的联合表是否实际上不是您可以在数据库设计中使用的东西。 IE。对所有项目使用一个表格，并有一个额外的“种类”列。