SQL关联多表查询个人衣物时返回0行的问题如何解决？

你执行的关联查询返回0行有两个核心原因：

1. SQL默认的JOIN是内连接（INNER JOIN），要求同时满足所有关联条件。但wardrobe表每行的cid仅对应单个品类的ID，比如鞋子类的sho-1不可能匹配衬衫表的Clothes_ID，没有任何一行能同时匹配鞋子、衬衫两个品类表的关联条件，自然返回0条结果。
2. 你示例中的衬衫表名为Shirts，但你写的SQL中关联的表名为shirt，表名拼写不一致也会导致查询异常，注意需和实际表名保持一致。

你要输出每行对应用户全品类持有情况的结果，可用条件聚合+左连接的方式实现，示例如下：

SELECT 
  w.owner_id AS 用户ID,
  'Paul' AS 用户姓名, -- 如有单独用户表可关联用户表取姓名
  MAX(CASE WHEN w.Type = 'Shoes' AND w.Date_own_to = '31-12-2099' THEN CONCAT(w.Name, ' 尺码:', sho.Size) END) AS 当前持有鞋子,
  MAX(CASE WHEN w.Type = 'Shirt' AND w.Date_own_to = '31-12-2099' THEN CONCAT(w.Name, ' 尺码:', shi.Size) END) AS 当前持有衬衫,
  MAX(CASE WHEN w.Type = 'Sockets' AND w.Date_own_to = '31-12-2099' THEN CONCAT(w.Name, ' 尺码:', soc.Size) END) AS 当前持有袜子
FROM wardrobe w
-- 用左连接关联各品类表，避免非对应品类的行被过滤
LEFT JOIN Shoes sho ON sho.Clothes_ID = w.cid AND w.Type = 'Shoes'
LEFT JOIN Shirts shi ON shi.Clothes_ID = w.cid AND w.Type = 'Shirt'
LEFT JOIN Sockets soc ON soc.Clothes_ID = w.cid AND w.Type = 'Sockets'
WHERE w.owner_id = 'poa' -- 需查询所有用户则去掉该条件
GROUP BY w.owner_id;

如果不需要获取品类表的尺码等额外信息，也可以去掉对品类表的关联，直接从wardrobe表取名称即可。

你需要按时间段筛选、输出两行对应两双鞋子的结果，可以以时间段内有效的鞋子记录为基础行，分别关联同时段有效的衬衫、袜子记录即可，示例如下：

-- 定义筛选时间范围，可灵活修改
SET @filter_start = '01-01-2021';
SET @filter_end = '31-12-2021';

SELECT
  w.owner_id AS 用户ID,
  'Paul' AS 用户姓名,
  w.Name AS 时间段内持有鞋子名称,
  shi.Name AS 对应有效衬衫名称,
  soc.Name AS 对应有效袜子名称
FROM wardrobe w
-- 关联同一用户同时段有效的衬衫
LEFT JOIN wardrobe shi 
  ON shi.owner_id = w.owner_id 
  AND shi.Type = 'Shirt'
  AND shi.Date_own_from <= @filter_end
  AND shi.Date_own_to >= @filter_start
-- 关联同一用户同时段有效的袜子
LEFT JOIN wardrobe soc 
  ON soc.owner_id = w.owner_id 
  AND soc.Type = 'Sockets'
  AND soc.Date_own_from <= @filter_end
  AND soc.Date_own_to >= @filter_start
WHERE
  w.owner_id = 'poa'
  AND w.Type = 'Shoes'
  -- 筛选出时间段内有效的鞋子记录，作为基础行
  AND w.Date_own_from <= @filter_end
  AND w.Date_own_to >= @filter_start;

上述SQL会返回2行结果，分别对应两双在2021年有效的鞋子，每行都会带上同时段有效的衬衫、袜子数据，符合你的预期。

内容的提问来源于stack exchange，提问作者Louis Chopard