如何在大型SQL查询中复用条件？是否支持占位符与变量？

当然可以！完全不用手动挨个替换重复条件——SQL里有好几种方案能帮你实现一处修改，全查询同步更新的需求，我结合你的示例给你拆解下常用的几种：

1. 用数据库变量+动态SQL（适合临时查询） #

不同数据库的变量语法略有差异，核心思路是把重复条件定义成变量，然后在动态SQL里引用它。举几个主流数据库的例子：

MySQL 写法 #

-- 先定义重复条件为变量
SET @date_condition = "e.exampleTableDate BETWEEN '2018-01-01' AND '2018-01-10'";

-- 用动态SQL执行查询，变量会自动替换到对应位置
PREPARE stmt FROM "
SELECT c.name,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND ?) AS stuff1,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND condition3 AND ?) AS stuff2,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND ?) AS stuff3
FROM country c
ORDER BY c.name";
EXECUTE stmt USING @date_condition, @date_condition, @date_condition;
DEALLOCATE PREPARE stmt;

SQL Server 写法 #

-- 定义变量（注意字符串里的单引号要转义成两个）
DECLARE @date_condition NVARCHAR(100) = 'e.exampleTableDate BETWEEN ''2018-01-01'' AND ''2018-01-10''';

-- 拼接动态SQL并执行
DECLARE @sql NVARCHAR(MAX) = '
SELECT c.name,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND ' + @date_condition + ') AS stuff1,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND condition3 AND ' + @date_condition + ') AS stuff2,
       (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND ' + @date_condition + ') AS stuff3
FROM country c
ORDER BY c.name';
EXEC sp_executesql @sql;

2. 用CTE（公共表表达式）封装过滤逻辑（更简洁的静态SQL） #

如果你的重复条件是用来过滤x和y表的，可以先把过滤后的数据集用CTE定义好，后面的子查询直接引用这个CTE，这样重复条件只需要写一次：

WITH filtered_x_y AS (
    -- 这里只写一次重复的日期条件
    SELECT x.id, y.*
    FROM x LEFT JOIN y
    WHERE e.exampleTableDate BETWEEN '2018-01-01' AND '2018-01-10'
)
SELECT c.name,
       -- 后面直接用过滤好的CTE，只加额外条件
       (SELECT DISTINCT id FROM filtered_x_y WHERE condition1 AND condition2) AS stuff1,
       (SELECT DISTINCT id FROM filtered_x_y WHERE condition1 AND condition2 AND condition3) AS stuff2,
       (SELECT DISTINCT id FROM filtered_x_y WHERE condition1) AS stuff3
FROM country c
ORDER BY c.name;

这种方法不用写动态SQL，可读性更高，适合条件固定、只是需要复用过滤结果的场景。

3. 用存储过程/函数（适合长期复用的查询） #

如果这个查询是你经常要运行的，把它做成存储过程，把重复的条件（比如日期范围）设为参数，每次调用只需要传参数值就行，完全不用改SQL本身：

SQL Server 存储过程示例 #

CREATE PROCEDURE GetCountryRelatedStuff
    @start_date DATE,
    @end_date DATE
AS
BEGIN
    SELECT c.name,
           (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND e.exampleTableDate BETWEEN @start_date AND @end_date) AS stuff1,
           (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND condition2 AND condition3 AND e.exampleTableDate BETWEEN @start_date AND @end_date) AS stuff2,
           (SELECT DISTINCT id FROM x LEFT JOIN y WHERE condition1 AND e.exampleTableDate BETWEEN @start_date AND @end_date) AS stuff3
    FROM country c
    ORDER BY c.name;
END;

-- 调用的时候只需要传新的日期参数
EXEC GetCountryRelatedStuff '2018-01-01', '2018-01-10';

总结一下 #

• 临时查询：优先用CTE（静态SQL，易读）或者动态变量（灵活）
• 长期复用的查询：写存储过程，参数化管理条件，维护起来最省心

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