时间戳

首页 > 解决方案 > SQL自连接表，默认为null并删除重复项

mysql - SQL自连接表，默认为null并删除重复项

问题描述

我有一个 SQL 模型（MySQL v8.0.20），它描述了一张有 1 个或 2 个面的物理卡。因为卡片面可以出现在多张物理卡片上，所以我使用连接表将其建模为多对多关系cardFaces。

我现在正在尝试获取不同正面卡片的列表。也就是说，如果一张卡片只有 1 个面，它会被表示一次，如果它有 2 个面，它将被表示两次（每侧正面一次）。

输入

连接表目前看起来像这样（为问题而简化）：

SELECT * FROM cardFaces;

+----+--------+--------+
| id | cardId | faceId |
+----+--------+--------+
|  1 |      A |      1 |
|  2 |      B |      2 |
|  3 |      B |      3 |
+----+--------+--------+

预期产出

我期望达到的结果是：

+--------+-------------+------------+
| cardId | frontFaceId | backFaceId |
+--------+-------------+------------+
|      A |           1 |       NULL |
|      B |           2 |          3 |
|      B |           3 |          2 |
+--------+-------------+------------+

电流输出

到目前为止，我只进行了自我加入和删除重复项，但我不知道如何引入只有一张面NULL的backFaceId卡片。

SELECT frontFace.cardId, frontFace.faceId frontFaceId, backFace.faceId backFaceId
FROM cardFaces frontFace
LEFT JOIN cardFaces backFace
ON frontFace.cardId = backFace.cardId
WHERE backFace.id != frontFace.id;

+--------+-------------+------------+
| cardId | frontFaceId | backFaceId |
+--------+-------------+------------+
|      B |           2 |          3 |
|      B |           3 |          2 |
+--------+-------------+------------+

标签： mysqlsql

解决方案

将where条件移至on子句：

SELECT frontFace.cardId, frontFace.faceId frontFaceId, backFace.faceId backFaceId
FROM cardFaces frontFace LEFT JOIN
     cardFaces backFace
     ON frontFace.cardId = backFace.cardId AND
        backFace.id <> frontFace.id;

NULLs 几乎所有的比较都失败了，包括<>将外连接变成内连接。

推荐阅读