MariaDB/MySQL WITH子句元素过多错误的解决方案咨询

核心思路是抛弃大量独立WITH子查询的写法，把多个筛选条件合并成单条查询逻辑，既避开WITH子句数量限制，又大幅提升查询效率。以下分场景给出改写后的SQL：

1. 仅「Find」操作（OR逻辑：物质属于任意指定清单） #

直接通过IN筛选目标清单，一次获取所有符合条件的物质ID：

CREATE TABLE IF NOT EXISTS filtering_1745
AS
SELECT DISTINCT substance_id AS `id`
FROM filters_substances
WHERE filter_id IN (196, 197, 198, /* 此处填入所有需要筛选的filter_id */);

2. 「Find + Exclude」组合操作（OR逻辑Find，排除指定清单的物质） #

写法一：用NOT IN实现排除 #

适合排除清单数量较少的场景：

CREATE TABLE IF NOT EXISTS filtering_1745
AS
SELECT DISTINCT fs.substance_id AS `id`
FROM filters_substances fs
WHERE fs.filter_id IN (196, 197, 198) /* Find的目标清单ID */
AND fs.substance_id NOT IN (
    SELECT substance_id 
    FROM filters_substances 
    WHERE filter_id IN (200, 201) /* 需要排除的清单ID */
);

写法二：用LEFT JOIN实现排除 #

避免NOT IN遇到空值时的潜在问题，适合排除清单数量大的场景：

CREATE TABLE IF NOT EXISTS filtering_1745
AS
SELECT DISTINCT fs.substance_id AS `id`
FROM filters_substances fs
LEFT JOIN filters_substances fs_exclude
    ON fs.substance_id = fs_exclude.substance_id
    AND fs_exclude.filter_id IN (200, 201) /* 需要排除的清单ID */
WHERE fs.filter_id IN (196, 197, 198) /* Find的目标清单ID */
AND fs_exclude.substance_id IS NULL;

3. 「Find」操作的AND逻辑（物质必须同时属于所有指定清单） #

通过分组统计匹配的清单数量，确保物质符合全部条件：

CREATE TABLE IF NOT EXISTS filtering_1745
AS
SELECT substance_id AS `id`
FROM filters_substances
WHERE filter_id IN (196, 197, 198) /* 需要同时匹配的清单ID */
GROUP BY substance_id
HAVING COUNT(DISTINCT filter_id) = 3; /* 数字需与指定的清单数量一致 */

如需结合Exclude，在上述基础上追加排除逻辑即可：

CREATE TABLE IF NOT EXISTS filtering_1745
AS
SELECT fs.substance_id AS `id`
FROM filters_substances fs
LEFT JOIN filters_substances fs_exclude
    ON fs.substance_id = fs_exclude.substance_id
    AND fs_exclude.filter_id IN (200, 201) /* 需要排除的清单ID */
WHERE fs.filter_id IN (196, 197, 198) /* 需要同时匹配的清单ID */
GROUP BY fs.substance_id
HAVING COUNT(DISTINCT fs.filter_id) = 3
AND fs_exclude.substance_id IS NULL;

额外优化建议 #

给filters_substances表创建复合索引：

CREATE INDEX idx_filter_substance ON filters_substances (filter_id, substance_id);

这个索引能让所有上述查询的执行效率大幅提升，尤其是在数据量较大的场景下。

对原思路的补充说明 #

你之前考虑的「执行60条单独SELECT再合并」的方式，会产生大量数据库请求，不仅效率低下，还额外增加了重复数据处理的成本，完全没必要采用。上述合并后的写法既能解决WITH子句超限问题，又能保证查询性能。

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