MySQL 使用 CASE WHEN ELSE 根据另一列和子查询中的值过滤产品 ID？

Question

好吧，我迷路了......我只是花了 3 个小时看这个，调整它，扭曲它，重写它，重新排列它，但我无法弄清楚。

我拥有的是多个表，我正在加入这些表来获取我需要的相关信息，显然......所以像这样(非常简化):

 job_id | part_id | part_title | override | override_id
 1      | 5       | part1      | 1        | 7
 1      | 6       | part2      | 1        | 7
 1      | 7       | part3_kit  | 1        | NULL
 1      | 8       | part4      | 1        | 9
 1      | 9       | part5_kit  | 1        | NULL
 1      | 10      | part6      | 1        | NULL
 1      | 11      | part7      | 1        | 15

前提很简单 - 有一个零件和零件套件的集合，其中零件套件包含特定零件(想想零件组)。

如果“override”字段已打开(= 1)，那么我只想显示以下部分:1)没有“override_id”(override_id IS NULL)并且2)其他部分具有引用的这些部分的“part_id”在其“override_id”列中，并且 3) 可能在“override_id”列中具有值，但引用的“part_id”不在给定结果内，因此它们无法替换，也需要包含在内。

反之亦然，如果“覆盖”被关闭(= 0)，那么我想显示所有不是套件的部件(套件=其他部件在“override_id”列中引用它们)。

因此，例如，如果 override = 1，则应列出的part_ids 7, 9 10, 11(7 和 9 在“override_id”列中被引用为套件，10 只是一个没有覆盖的零件引用/不是套件，11 是单个部件，其中引用了覆盖部件，但该部件不在现有结果列表中，因此该单个部件不会被套件替换，而是被包含在内)。

如果 override = 0，则应列出的部分 ID 为 5、6、9、10、11(同样，7 和 9 在这些特定结果的“override_id”列中被引用为套件，因此这些结果应该被省略，而 11 应该被包含，因为它的覆盖部分 id 15 不包含在结果中，因此第 11 部分不能被覆盖部分替换 - 它也需要被包含)。

注意:“覆盖”字段是通过 JOIN 从另一个外部表(全局设置为 0 或 1，绝不是两者的混合)中提取的，但为了论证和简单起见，我将其包含在此处此单个表的一部分。

这是我一直在尝试组合的查询，但我到处都遇到了障碍:

SELECT
j.job_id,
pt.part_id,
pt.part_title,
pt.override,
pt.override_id,
(SELECT
    GROUP_CONCAT(DISTINCT pt.override_id)
    FROM job j
    INNER JOIN part_type pt ON j.part_id = pt.part_id
    WHERE j.job_id = 1
    AND pt.override_id IS NOT NULL) AS override_ids
FROM job j
INNER JOIN part_type pt ON j.part_id = pt.part_id
WHERE j.job_id = 1
AND pt.part_id NOT IN (
    CASE pt.override 
    WHEN '1' THEN IF(pt.override_id IS NOT NULL, pt.part_id, NULL)
    WHEN '0' THEN override_ids
    END)
GROUP BY j.job_id, pt.part_title
ORDER BY pt.part_title;

对于上面的查询，我收到错误消息:

Error in query (1054): Unknown column 'override_ids' in 'where clause'

如果我将该 SELECT 子查询从顶部移至我的 CASE WHEN 部分:

SELECT
j.job_id,
pt.part_id,
pt.part_title,
pt.override,
pt.override_id
FROM job j
INNER JOIN part_type pt ON j.part_id = pt.part_id
WHERE j.job_id = 1
AND pt.part_id NOT IN (
    CASE pt.override 
    WHEN '1' THEN IF(pt.override_id IS NOT NULL, pt.part_id, NULL)
    WHEN '0' THEN (SELECT
        GROUP_CONCAT(DISTINCT pt.override_id)
        FROM job j
        INNER JOIN part_type pt ON j.part_id = pt.part_id
        WHERE j.job_id = 1
        AND pt.override_id IS NOT NULL)
    END)
GROUP BY j.job_id, pt.part_title
ORDER BY pt.part_title;

GROUP_CONCAT 返回一个字符串而不是整数列表，因此插入到父级 NOT IN (...) 中，它仅计算第一个值，而不是所有值。在这种情况下，它将返回 7,9，但只有 7 会在 NOT IN (...) 部分进行计算。

现在，如果我从子查询中删除 GROUP_CONCAT 并将其保留为 SELECT DISTINCT pt.override_id FROM... 我会收到一条错误消息:

Error in query (1242): Subquery returns more than 1 row

所以我只是迷失在这里......没有想法。

有人帮忙吗？

我希望我有更多的 MySQL 经验来解决这个问题，但到目前为止，我已经尽我所能和我的知识水平尝试了一切，3 小时后我已经接近最终目标了，但事实并非如此。

我错过了什么？

如果您发现该查询可以进一步优化或简化，请随时发表评论，同样，我仍在学习，我可能遗漏了一些对你们专家来说显而易见的东西......

提前致谢!

Answer 1

好吧，经过一个多小时的摆弄和移动东西之后，我已经成功地涵盖了所有条件并使查询能够正确评估。

这是我得到的最终查询，但请随意提出更优化的解决方案(感谢 FIND_IN_SET 建议，完全忘记了这个建议，因为这将采用 GROUP_CONCAT 字符串值并对其进行完全评估，这与 NOT IN 不同)。

SELECT
j.job_id,
pt.part_id,
pt.part_title,
pt.override,
pt.override_id
FROM job j
INNER JOIN part_type pt ON j.part_id = pt.part_id
WHERE j.job_id = 1
AND
    CASE pt.override 
        WHEN '1' THEN IF(pt.override_id IS NULL, 
                         pt.override_id IS NULL, 
                         NOT FIND_IN_SET(pt.override_id,
        (SELECT
            GROUP_CONCAT(DISTINCT pt.part_id)
            FROM job j
            INNER JOIN part_type pt ON j.part_id = pt.part_id
            WHERE j.job_id = 1
        ))) 
        WHEN '0' THEN NOT FIND_IN_SET(pt.part_id, 
        (SELECT
            GROUP_CONCAT(DISTINCT pt.override_id)
            FROM job j
            INNER JOIN part_type pt ON j.part_id = pt.part_id
            WHERE j.job_id = 1
            AND pt.override_id IS NOT NULL))
    END
GROUP BY j.job_id, pt.part_title
ORDER BY pt.part_title;

我只是不知道 CASE WHEN 也可以应用于 WHERE/AND 条件...非常方便!