如何使用SQL子查询限定父查询返回的数值范围

实现方案 #

因为你的子查询固定返回单行数据，有两种常用的实现方式，你可以根据自己的使用场景选择：

方案1：直接在WHERE条件中嵌入标量子查询 #

这种写法简单直观，适合临时查询的场景，大多数数据库会对重复的单行子查询做结果缓存，不会有明显的性能损耗：

SELECT
    ds.pre,
    ds.sze,
    ds.fdm_pre
FROM
    form_data_stage ds
WHERE
    ds.pre > (SELECT pre FROM form_data_stage WHERE fdm_pre = 10 AND pre < 16 AND (pre + sze) > 16)
    AND ds.pre < (SELECT pre + sze FROM form_data_stage WHERE fdm_pre = 10 AND pre < 16 AND (pre + sze) > 16)

方案2：先通过子查询/CTE提取范围参数再关联查询 #

这种写法子查询只会执行一次，性能更优，而且范围逻辑集中维护，适合业务代码中使用：

WITH range_param AS (
    -- 定义范围的子查询，提前计算好区间的上下限
    SELECT
        pre AS range_start,
        pre + sze AS range_end
    FROM 
        form_data_stage
    WHERE
        fdm_pre = 10
        AND pre < 16 AND (pre + sze) > 16
)
SELECT
    ds.pre,
    ds.sze,
    ds.fdm_pre
FROM
    form_data_stage ds, range_param rp
WHERE
    ds.pre > rp.range_start
    AND ds.pre < rp.range_end

如果你的数据库不支持CTE语法，也可以把范围子查询直接写在FROM子句中：

SELECT
    ds.pre,
    ds.sze,
    ds.fdm_pre
FROM
    form_data_stage ds,
    (
        SELECT
            pre AS range_start,
            pre + sze AS range_end
        FROM 
            form_data_stage
            WHERE fdm_pre = 10 AND pre < 16 AND (pre + sze) > 16
    ) rp
WHERE
    ds.pre > rp.range_start
    AND ds.pre < rp.range_end

注意：如果子查询存在返回空值的可能，又需要父查询返回全量数据，可以把上面的笛卡尔积关联改成LEFT JOIN，再根据自己的需求补充空值判断逻辑即可。

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