如何仅使用JOIN类语句合并两张餐厅表的PID与QTY列数据

解决方案 #

你之前的写法是将两个餐厅的库存数据横向关联到了不同列，无法得到纵向拼接的行结果，仅用JOIN实现纵向拼接的可行写法如下：

SELECT 
  il.PID,
  CASE WHEN src.flag = 1 THEN r1.QTY ELSE r2.QTY END AS QTY
FROM (SELECT 1 AS flag UNION ALL SELECT 2 AS flag) src
CROSS JOIN ItemList il
LEFT JOIN Restaurant1 r1 
  ON il.PID = r1.PID AND src.flag = 1
LEFT JOIN Restaurant2 r2 
  ON il.PID = r2.PID AND src.flag = 2
WHERE COALESCE(r1.QTY, r2.QTY) IS NOT NULL
ORDER BY src.flag, il.PID;

如果你的数据库支持VALUES语法，临时标记表的构造可以替换为FROM (VALUES (1), (2)) AS src(flag)，无需使用UNION。

实现逻辑 #

1. 首先构造一个只有两行、标记为1和2的临时数据源src，分别对应取Restaurant1、Restaurant2的数据的场景
2. 用CROSS JOIN（交叉连接，属于JOIN类语法）把ItemList的所有PID和这两个标记做笛卡尔积，得到每一个PID对应两个标记的中间结果
3. 分别左连接两个餐厅表，连接条件除了PID匹配外，额外加上标记匹配：标记为1时只关联Restaurant1的数据，标记为2时只关联Restaurant2的数据
4. 过滤掉两个餐厅库存都为空的无效行，最终输出的结果就是先列出Restaurant1的所有库存记录，再列出Restaurant2的所有库存记录，和你要求的拼接格式完全一致

内容的提问来源于stack exchange，提问作者Sway the Destroyer 'STD'