mysql - 用于查找缺失条目的 SQL 查询

Question

我有一个数据库，我需要在其中找到一些缺失的条目并填写它们。我有一个名为“菜单”的表，每个餐厅都有多个菜肴，每个菜肴有 4 个不同的语言条目(实际上主数据库中有 8 个，但为了简单起见，我们使用 4 个)，我需要找出特定餐厅的哪些菜肴是缺少任何语言条目。

select * from menu where restaurantid = 1

我被困在那里，类似于语言 1 或 2 或 3 或 4 不存在的地方，这是复杂的一点，因为我需要查看存在的语言才能看到缺少的语言，因为我无法显示不存在的东西。我希望这是有道理的？

在下面的示例表中，餐厅 2 的菜品 2 缺少语言 3，这就是我需要找到的。

+--------------+--------+----------+-----------+
| RestaurantID | DishID | DishName | Language  |
+--------------+--------+----------+-----------+
|            1 |      1 | Soup     |        1  |
|            1 |      1 | Soúp     |        2  |
|            1 |      1 | Soupe    |        3  |
|            1 |      1 | Soupa    |        4  |
|            1 |      2 | Bread    |        1  |
|            1 |      2 | Bréad    |        2  |
|            1 |      2 | Breade   |        3  |
|            1 |      1 | Breada   |        4  |
|            2 |      1 | Dish1    |        1  |
|            2 |      1 | Dísh1    |        2  |
|            2 |      1 | Disha1   |        3  |
|            2 |      1 | Dishe1   |        4  |
|            2 |      2 | Dish2    |        1  |
|            2 |      2 | Dísh2    |        2  |
|            2 |      2 | Dishe2   |        4  |
+--------------+--------+----------+-----------+

Answer 1

就性能而言，反连接模式通常是最有效的。

您的特殊情况有点棘手，因为您需要“生成”丢失的行。如果每个 (ResturantID,DishID) 应有 4 行，语言值为 1、2、3 和 4，我们可以使用 CROSS JOIN 操作生成所有行的集合。

下一步是将反连接...左外部连接应用到菜单表中存在的行，以便我们从 CROSS JOIN 集中获取所有行以及匹配的行。

“技巧”是在 WHERE 子句中使用谓词来过滤掉找到匹配项的行，这样我们就剩下没有匹配项的行。

(一开始似乎有点奇怪，但是一旦你的大脑了解了反连接模式，它就会变得熟悉。)

因此这种形式的查询应该返回指定的结果集。

 SELECT d.RestaurantID
      , d.DishID
      , lang.id AS missing_language
   FROM (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        ) lang
  CROSS
   JOIN (SELECT e.RestaurantID, e.DishID
           FROM menu e
          GROUP BY e.RestaurantID, e.DishID
        ) d
   LEFT
   JOIN menu m
     ON m.RestaurantID = d.RestaurantID
    AND m.DishID       = d.DishID
    AND m.Language     = lang.id
  WHERE m.RestaurantID IS NULL
 ORDER BY 1,2,3

让我们来解压一下。

首先我们得到一个包含数字 1 到 4 的集合。

接下来我们得到一个包含(RestaurantID, DishID)不同元组的集合。 (对于每个不同的餐厅，都有一个不同的 DishID 列表，只要该组合的任何语言至少有一行。)

我们进行交叉连接，将集合一 (lang) 中的每一行与集合 (d)，生成我们想要的每个(RestaurantID、DishID、语言)的“完整”集。

下一部分是反连接...左外连接到菜单，以查找“完整”集中的哪些行在菜单中具有匹配行，并过滤掉所有匹配的行。

这可能有点令人困惑。如果我们认为 CROSS JOIN 操作会生成一个类似于菜单表的临时表，但包含所有可能的行...我们可以用伪代码来思考它:

create temporary table all_menu_rows (RestaurantID, MenuID, Language) ; 
insert into all_menu_rows ... all possible rows, combinations ; 

那么反连接模式就更容易看到了:

 SELECT r.RestaurantID
      , r.DishID
      , r.Language
   FROM all_menu_rows r
   LEFT
   JOIN menu m
     ON m.RestaurantID = r.RestaurantID
    AND m.DishID       = r.DishID
    AND m.Language     = r.Language
  WHERE m.RestaurantID IS NULL
 ORDER BY 1,2,3

(但是我们不必承担创建和填充临时表的额外开销，我们可以在查询中直接执行此操作。)

<小时/>

当然，这不是唯一的方法。我们可以使用 NOT EXISTS 谓词代替反连接，尽管这通常效率不高。查询的第一部分是相同的，生成我们期望的“完整”行集；不同的是我们如何识别菜单表中是否有匹配的行:

 SELECT d.RestaurantID
      , d.DishID
      , lang.id AS missing_language
   FROM (SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        ) lang
  CROSS
   JOIN (SELECT e.RestaurantID, e.DishID
           FROM menu e
          GROUP BY e.RestaurantID, e.DishID
        ) d
  WHERE NOT EXISTS ( SELECT 1
                       FROM menu m
                      WHERE m.RestaurantID = d.RestaurantID
                        AND m.DishID       = d.DishID
                        AND m.Language     = lang.id
                   )
 ORDER BY 1,2,3

对于“完整”集中的每一行(由 CROSS JOIN 操作生成)，我们将运行一个相关子查询来检查是否找到匹配的行。如果未找到匹配行，则 NOT EXISTS 谓词返回 TRUE。 (这更容易理解一点，但它的性能通常不如反连接模式。)