mysql - 仅获取另一个表中(至多)1 列值的行

Question

我有一个在 PHP 中动态构建的查询，它使用一组连接在一起的子查询来选择对象值的不同组合...例如，给定值表:

[object_id]    [value_id]
99             1
99             2
99             3

我将得到以下结果:

[object_id]  [a0]   [a1]    [a2]
99           null   null    null  // empty is a valid combination
99           1      null    null
99           null   2       null
99           null   null    3
99           1      2       null
99           null   2       3
99           1      2       3

我有一个单独的表，列出了这些对象值的无效组合:

无效值

[object_id] [value]
  99         1
  99         2

我正在尝试使用无效值列表来过滤第一个查询的结果。更具体地说，如果 invalid_values 中存在 2 个或更多 a* 列作为值，则不包含该行。因此，使用此示例时，1 2 null 99 和 1 2 3 99 将不会包含在结果中，因为 1 和 2 都在记录中，并且都是无效值.

我有一个查询可以满足我的要求，但我担心它效率很低。对于我正在构建的每个子查询，我加入另一个选择逗号分隔的无效值的子查询。我用它来指示值列是否是“不兼容的候选者”。最后，我将所有不兼容的候选列相加，如果是 2 个或更多，则不会返回它们。以下是为“对象”id 99 构建的查询示例:

SELECT a0.value_id AS '0', a1.value_id AS '1', a2.value_id AS '2'
FROM (
    SELECT NULL AS value_id, 99 AS object_id, 0 AS incompatible_candidate
    UNION ALL
    SELECT value.value_id, value.object_id, CASE WHEN FIND_IN_SET(value.value_id, pivs.valueIds) THEN 1 ELSE 0 END AS incompatible_candidate
    FROM value
    JOIN (
        SELECT GROUP_CONCAT(iv.value_id ORDER BY iv.value_id ASC) AS valueIds
        FROM invalid_values iv
        WHERE iv.object_id = 99
        GROUP BY iv.object_id
    ) pivs
    WHERE value.value_id IN (1) AND value.object_id = 99
) AS a0
JOIN (
    SELECT NULL AS value_id, 99 AS object_id, 0 AS incompatible_candidate
    UNION ALL
    SELECT value.value_id, value.object_id, CASE WHEN FIND_IN_SET(value.value_id, pivs.valueIds) THEN 1 ELSE 0 END AS incompatible_candidate
    FROM value
    JOIN (
        SELECT GROUP_CONCAT(iv.value_id ORDER BY iv.value_id ASC) AS valueIds
        FROM invalid_values iv
        WHERE iv.object_id = 99
        GROUP BY iv.object_id
    ) pivs
    WHERE value.value_id IN (2) AND value.object_id = 99
) AS a1
JOIN (
    SELECT NULL AS value_id, 99 AS object_id, 0 AS incompatible_candidate
    UNION ALL
    SELECT value.value_id, value.object_id, CASE WHEN FIND_IN_SET(value.value_id, pivs.valueIds) THEN 1 ELSE 0 END AS incompatible_candidate
    FROM value
    JOIN (
        SELECT GROUP_CONCAT(iv.value_id ORDER BY iv.value_id ASC) AS valueIds
        FROM invalid_values iv
        WHERE iv.object_id = 99
        GROUP BY iv.object_id
    ) pivs
    WHERE value.value_id IN (3) AND value.object_id = 99
) AS a2
GROUP BY a0.value_id, a1.value_id, a2.value_id
HAVING SUM(a0.incompatible_candidate + a1.incompatible_candidate + a2.incompatible_candidate) < 2;

任何人都可以帮助我构建更高效的查询吗？

Answer 1

使用排列表检测无效行，即具有两个或多个无效值的行。您可以更改为< 2对于有效行。

<强> SQL DEMO

SELECT * 
FROM Permutation P
LEFT JOIN Invalid  x
  ON (P.`a0`, P.`object_id`) = (x.`value_id`, x.`object_id`)
LEFT JOIN Invalid  y
  ON (P.`a1`, P.`object_id`) = (y.`value_id`, y.`object_id`)
LEFT JOIN Invalid  z
  ON (P.`a2`, P.`object_id`) = (z.`value_id`, z.`object_id`)
 AND P.`object_id` = z.`object_id`
WHERE CASE WHEN x.`value_id` IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN y.`value_id` IS NULL THEN 0 ELSE 1 END + 
      CASE WHEN z.`value_id` IS NULL THEN 0 ELSE 1 END 
      >= 2
;

输出