MySQL查询故障排查：筛选delivery表关联状态全为6的记录失败

问题背景 #

我有两张MySQL数据表：

• printorders表：

• delivery表：

需求是：从delivery表中筛选出faktorids字段里所有faktor值，在printorders表中对应的status均为6的记录。

我尝试了下面的查询，但没得到预期结果：

SELECT *, 
( SELECT status FROM printorders WHERE faktor = (SUBSTRING(faktorids, 1, 9)) LIMIT 1 ) AS d1, 
( SELECT status FROM printorders WHERE faktor = (SUBSTRING(faktorids, 11, 9)) LIMIT 1 ) AS d2 
FROM delivery 
WHERE d1= 6 AND d2 = 6

问题分析 #

你的查询有两个核心问题：

1. WHERE子句无法引用SELECT中定义的别名
   MySQL的执行顺序是先处理WHERE子句，再处理SELECT子句生成字段别名。所以当WHERE d1=6 AND d2=6执行时，d1和d2这两个别名还没被计算出来，这会直接导致语法错误或者逻辑不生效。

2. 硬编码字符串截取的局限性
   你用SUBSTRING(faktorids, 1, 9)和SUBSTRING(faktorids, 11, 9)假设每个faktor都是9位，且faktorids里只有两个值。但如果faktor长度变化、分隔符位置偏移，或者faktorids里有3个及以上的faktor值，这个写法就完全失效了，根本无法覆盖所有场景。

正确的解决方法 #

要解决这个问题，核心思路是先把faktorids中的每个faktor拆分出来，再关联printorders表验证所有拆分后的faktor对应的status是否都是6，最后筛选符合条件的delivery记录。

方法1：使用递归CTE（MySQL 8.0+推荐）

这个方法可以动态拆分任意数量的faktor值，不需要硬编码长度：

WITH split_faktor AS (
    SELECT 
        d.id,
        -- 拆分出单个faktor值
        SUBSTRING_INDEX(SUBSTRING_INDEX(d.faktorids, '-', n.n), '-', -1) AS faktor
    FROM delivery d
    -- 生成数字序列，覆盖最多的faktor数量（这里写了5个，可根据实际调整）
    CROSS JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) n
    -- 只保留有效拆分的行
    WHERE n.n <= LENGTH(d.faktorids) - LENGTH(REPLACE(d.faktorids, '-', '')) + 1
)
-- 筛选所有关联的faktor状态都是6的delivery记录
SELECT DISTINCT d.*
FROM delivery d
LEFT JOIN split_faktor sf ON d.id = sf.id
LEFT JOIN printorders po ON sf.faktor = po.faktor
GROUP BY d.id, d.faktorids
-- 统计不符合条件的数量：status不是6，或者printorders里没有该faktor
HAVING SUM(CASE WHEN po.status != 6 OR po.status IS NULL THEN 1 ELSE 0 END) = 0;

方法2：兼容MySQL 5.x的写法（子查询拆分）

如果你的MySQL版本低于8.0，没有CTE功能，可以用子查询模拟拆分逻辑（这里以最多2个faktor为例，若有更多需要扩展）：

SELECT d.*
FROM delivery d
-- 检查第一个faktor的状态
JOIN printorders po1 ON po1.faktor = SUBSTRING_INDEX(d.faktorids, '-', 1)
-- 检查第二个faktor的状态（如果有）
LEFT JOIN printorders po2 ON po2.faktor = SUBSTRING_INDEX(d.faktorids, '-', -1)
WHERE po1.status = 6
-- 如果第二个faktor存在，也必须满足status=6
AND (po2.status IS NULL OR po2.status = 6);

注意：这个写法只适用于faktorids最多2个值的场景，若有更多值需要继续添加JOIN条件。

修复你原来的查询（仅作学习参考） #

如果你只是想快速修复原来的查询（仅限固定2个9位faktor的场景），可以把查询放到子查询中，让别名先被计算出来：

SELECT *
FROM (
    SELECT *, 
        (SELECT status FROM printorders WHERE faktor = SUBSTRING(faktorids, 1, 9) LIMIT 1) AS d1, 
        (SELECT status FROM printorders WHERE faktor = SUBSTRING(faktorids, 11, 9) LIMIT 1) AS d2 
    FROM delivery 
) AS temp
WHERE d1 = 6 AND d2 = 6;

但还是不推荐这个写法，因为它的局限性太强，无法应对动态的faktor数量和长度。

内容的提问来源于stack exchange，提问作者parvaz