如何优化SQL查询以避免子查询中的重复日期计算代码

你可以通过以下两种常用方案避免重复计算日期边界，同时还能简化原查询的嵌套逻辑，提升执行效率： #

方案1：使用CTE（公用表表达式）预计算日期边界 #

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持CTE的主流数据库，一次性计算出两个固定日期值，全查询可直接复用：

WITH date_boundary AS (
    SELECT 
        MIN(order_date) AS min_date,
        DATE_ADD(MIN(order_date), INTERVAL 10 YEAR) AS max_date
    FROM orders
)
SELECT c.name, o.price
FROM customers c
JOIN orders o ON o.id = c.order_id
-- 直接复用预计算的日期边界
WHERE o.order_date BETWEEN (SELECT min_date FROM date_boundary) AND (SELECT max_date FROM date_boundary)
-- 原嵌套子查询找最高价格可直接用MAX函数简化
AND o.price = (
    SELECT MAX(price) 
    FROM orders o2
    JOIN date_boundary db ON o2.order_date BETWEEN db.min_date AND db.max_date
)

方案2：使用用户变量（兼容MySQL低版本） #

如果你的数据库版本不支持CTE，可以用用户变量先存储计算好的日期值，后续查询直接调用：

-- 仅执行一次日期计算，存入变量
SELECT 
    MIN(order_date),
    DATE_ADD(MIN(order_date), INTERVAL 10 YEAR)
INTO @min_order_date, @max_order_date
FROM orders;

-- 主查询直接复用变量，无重复计算
SELECT c.name, o.price
FROM customers c
JOIN orders o ON o.id = c.order_id
WHERE o.order_date BETWEEN @min_order_date AND @max_order_date
AND o.price = (SELECT MAX(price) FROM orders WHERE order_date BETWEEN @min_order_date AND @max_order_date);

补充优化建议 #

• 原语句的多层嵌套子查询可以直接简化为上述写法，不需要多次筛选order_id再关联，执行效率更高
• 建议给orders.order_date、orders.price字段添加索引，可进一步提升查询速度

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