SQL重构SELECT查询：如何实现复杂WHERE子句的复用

现有UNION查询存在3处重复的WHERE判断逻辑，原本简单的is_chosen判断需要替换为多条件组合的复杂过滤规则，重复编写维护成本高、易出错。此前尝试使用WITH子句实现逻辑复用未达到预期效果。

初始简单逻辑版本查询：

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  where p.is_chosen
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  where not p.is_chosen and c.is_chosen
) x

替换复杂条件后的待简化查询（标记部分逻辑完全一致）：

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  -- 重复逻辑1
  where p.is_chosen and p.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = p.id and i.last_active is not null) > 5
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  -- 重复逻辑2（取反）、重复逻辑3
  where not (p.is_chosen and p.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = p.id and i.last_active is not null) > 5) and (c.is_chosen and c.last_active > now() - interval '7' day and (select count(*) from items i where i.project_id = c.id and i.last_active is not null) > 5)
) x

方案1：CTE预计算合格集合（推荐，无额外依赖、性能好） #

之前WITH子句使用无效，核心原因是没有将公共判断逻辑下沉到CTE层做统一预计算。正确做法是先在CTE中一次性计算出所有符合筛选规则的项目ID，后续UNION分支直接关联该结果集做判断，不需要重复编写过滤条件。

简化后代码：

WITH qualified_projects AS (
  -- 复杂筛选逻辑仅在此处编写1次，后续修改规则仅需调整此处
  SELECT p.id
  FROM projects p
  WHERE 
    p.is_chosen 
    AND p.last_active > NOW() - INTERVAL '7' day 
    AND (SELECT COUNT(*) FROM items i WHERE i.project_id = p.id AND i.last_active IS NOT NULL) > 5
)
SELECT DISTINCT * FROM (
  -- 查询符合条件的父项目：父项目在合格列表中，存在关联子项目
  SELECT p.*
  FROM projects p
  JOIN projects c ON c.parent_id = p.id
  INNER JOIN qualified_projects qp ON qp.id = p.id

  UNION ALL

  -- 查询符合条件的子项目：父项目不在合格列表，子项目本身在合格列表
  SELECT c.*
  FROM projects p
  JOIN projects c ON c.parent_id = p.id
  LEFT JOIN qualified_projects qp_p ON qp_p.id = p.id
  INNER JOIN qualified_projects qp_c ON qp_c.id = c.id
  WHERE qp_p.id IS NULL
) x

该方案的优势：

• 过滤逻辑仅维护1处，修改规则不需要同步调整多个位置
• 预计算合格项目ID后，后续关联为主键ID匹配，相比重复执行关联计数子查询性能更优
• 不需要创建额外数据库对象，兼容所有支持CTE语法的数据库（PostgreSQL 8.4+、MySQL 8.0+、SQL Server等主流数据库均支持）

方案2：自定义函数封装（适合跨多查询复用规则的场景） #

如果这套项目筛选规则需要在多个不同业务查询中使用，可以创建自定义标量函数，传入项目ID即可返回是否符合筛选条件，所有查询直接调用函数即可复用逻辑。

以PostgreSQL为例的函数定义：

CREATE OR REPLACE FUNCTION is_project_qualified(bigint)
RETURNS BOOLEAN AS $$
SELECT EXISTS (
  SELECT 1
  FROM projects p
  WHERE 
    p.id = $1
    AND p.is_chosen 
    AND p.last_active > NOW() - INTERVAL '7' day 
    AND (SELECT COUNT(*) FROM items i WHERE i.project_id = p.id AND i.last_active IS NOT NULL) > 5
)
$$ LANGUAGE sql STABLE;

函数创建完成后，原查询可以直接简化为和初始简单版本几乎一致的结构：

select distinct *
from (
  select p.* -- finding parents
  from projects p
  join projects c on c.parent_id = p.id
  where is_project_qualified(p.id)
 union all
  select c.* -- finding children
  from projects p
  join projects c on c.parent_id = p.id
  where not is_project_qualified(p.id) and is_project_qualified(c.id)
) x

注意：该方案需要数据库的函数创建权限，定义函数时需要标记正确的易变性等级（如上例中的STABLE），避免查询优化器无法生成最优执行计划导致性能下降。

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