MySQL，还有什么时候可以根据另一列和子查询中的值筛选产品in？

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

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

如果“覆盖”字段被打开(= 1)，那么我只想显示以下部分: 1)没有"override_id“(override_id为NULL)；2)对于在"override_id”列中引用的这些部分，其他部分具有"part_id“，而3)在"override_id”列中可能有值，但引用的"part_id“不在给定的结果中，因此不能替换它们，因此也需要包括它们。

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

因此，例如，如果覆盖= 1，那么应该列出的是part_ids 7、9 10、11 (7和9在"override_id“列中作为工具包引用，10只是没有覆盖引用的单个部件/不是工具包，而11是带有重写部分的引用的单个部件，但该部分不在现有的结果列表中，因此单个部分没有被工具包替换，而是被包含在内)。

如果覆盖= 0，那么应该列出的是part_ids 5、6、9、10、11 (同样，7和9作为这些特定结果的"override_id“列中的工具包引用，因此应该省略这些结果，而11应该包括在结果中，因为其覆盖id 15不包括在结果中，因此第11部分不能替换为覆盖部分--也需要包括它)。

注意：“重写”字段是通过联接从另一个外部表(一个全局设置为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;

对于上面的查询，我得到了错误信息：

查询错误(1054)：'where子句‘中未知列'override_ids’

如果我将选择子查询从顶部移动到我的情况下，当部分：

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返回一个字符串，而不是一个整数列表，因此插入回父--而不是in (.)它只计算第一个值，而不是所有值。在这种情况下，它将返回7,9，但只有7将在非In (.)中计算。部分。

现在，如果我将GROUP_CONCAT从子查询中删除，并将其保留为SELECT pt.override_id，则从.我将此作为错误消息：

查询中的错误(1242)：子查询返回多于1行

所以我在这里迷路了..。毫无创意。

有人帮忙吗？

我希望我有更多的MySQL经验，以找到我的出路，但我已经尝试了我的能力和知识水平到目前为止，3个小时后，我已经接近最终目标，但不是真的。

我遗漏了什么？

如果您看到这个查询可以进一步优化或简化，可以随意评论，因为我仍然在学习，我可能遗漏了一些对专家来说很明显的东西.

提前感谢！

Answer 1

如果我理解正确的话，你有三组零件：

• 由另一个部件引用的部件，称为kit (#引用它> 0)
• 作为工具包一部分的部件(-> override_id <> null)
• 未被另一部分引用且不属于工具包的部分(->override_id为null和#引用它= 0)

因此，执行一个只计算这些值的(子)查询：

select
    pt.part_id,
    pt.part_title,
    (select count(*) 
     from part_type pt1
     where pt1.override_id = pt.part_id) as cntrefs,
    pt.override_id 
from part_type pt 

这将给你：

    part_id | part_title | cntrefs  | override_id
    5       | part1      | 0        | 7
    6       | part2      | 0        | 7
    7       | part3_kit  | 2        | NULL
    8       | part4      | 0        | 9
    9       | part5_kit  | 1        | NULL
    10      | part6      | 0        | NULL

根据您从哪里获得pr.override (我假设您在外部某个位置加入了一个pr表)，您现在可以从这个选择中筛选出以下两种情况：

select ... from ( <insert the above> ) as parts
join job j on j.part_id = parts.part_id
<insert pr.override from somewhere, something like "join options as pr">
where j.job_id = 1 and 
   ( ( pr.override = 1 and (parts.override_id is null))
   or ( pr.override = 0 and (parts.cntrefs > 0)) )
-- maybe you need a group here, depending on your "job"-table     
-- group by j.job_id, pt.part_id, ...

您将WHER-语句放在哪里，如果将其直接放在查询中，则取决于您的数据和覆盖标志，但您应该有这样的想法。

Answer 2

嗯，在花了一个多小时的时间摆弄它和移动东西之后，我成功地处理了所有的条件，并使查询能够正确地进行评估。

下面是我得到的最后一个查询，但可以建议更多的优化解决方案(感谢FIND_IN_SET建议，完全忘记了这个建议，因为这将使用GROUP_CONCAT字符串值，并与NOT完全不同)。

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;

我只是不知道什么时候才能适用.非常方便！