如何借助WITH子句消除SQL查询中job_id in ('1','2')的重复使用

嘿，这问题我太懂了！咱们可以用WITH子句（也就是CTE，公共表表达式）把重复的过滤条件抽出来，一次性定义好符合job_id IN ('1','2')的数据集，之后所有需要用到这个数据集的地方直接引用就行，彻底告别重复写条件的麻烦。

先看一下原来带有重复条件的查询（比如两次用到WHERE job_id IN ('1','2')）：

-- 原查询：重复写了两次job_id过滤条件
SELECT * 
FROM employees
WHERE job_id IN ('1','2')
AND salary > (SELECT AVG(salary) FROM employees WHERE job_id IN ('1','2'));

再看用WITH子句改造后的版本：

WITH filtered_employees AS (
    -- 这里一次性过滤出符合job_id条件的所有数据
    SELECT * 
    FROM employees
    WHERE job_id IN ('1','2')
)
-- 后续所有查询都直接用这个预过滤好的数据集
SELECT * 
FROM filtered_employees
WHERE salary > (SELECT AVG(salary) FROM filtered_employees);

如果你的查询里有多个子查询都用到了这个job_id过滤条件，比如这样：

-- 原查询：多个子查询重复写job_id条件
SELECT 
    (SELECT COUNT(*) FROM employees WHERE job_id IN ('1','2') AND department_id = 10) AS dept10_count,
    (SELECT COUNT(*) FROM employees WHERE job_id IN ('1','2') AND department_id = 20) AS dept20_count,
    (SELECT AVG(salary) FROM employees WHERE job_id IN ('1','2')) AS avg_salary;

用WITH改造后会更简洁，而且维护起来更方便——以后要修改job_id的取值，只需要在CTE里改一次就行：

WITH filtered_employees AS (
    SELECT * 
    FROM employees
    WHERE job_id IN ('1','2')
)
SELECT 
    (SELECT COUNT(*) FROM filtered_employees WHERE department_id = 10) AS dept10_count,
    (SELECT COUNT(*) FROM filtered_employees WHERE department_id = 20) AS dept20_count,
    (SELECT AVG(salary) FROM filtered_employees) AS avg_salary;

WITH子句定义的CTE是临时的，只在当前查询中有效，不会创建持久化的表，而且主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都支持这个语法，放心用就行～

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