PostgreSQL无关联列时计算列A上下界的方法问询

核心需求：对表A中colA的每个数值，从表B的colB中找到小于该值的最大数作为下界、大于该值的最小数作为上界（例如colA=20时，对应下界18、上界24）。以下是无关联场景下的几种实现方式：

方式1：标量子查询（简洁通用） #

适用于大多数SQL方言（MySQL、PostgreSQL、SQL Server等），通过每个colA值单独查询表B的对应边界值：

SELECT
    a.colA,
    -- 取小于当前colA的最大colB作为下界
    (SELECT MAX(colB) FROM tableB WHERE colB < a.colA) AS lower_bound,
    -- 取大于当前colA的最小colB作为上界
    (SELECT MIN(colB) FROM tableB WHERE colB > a.colA) AS upper_bound
FROM tableA a;

方式2：处理边界特殊情况 #

如果需要对colA小于所有colB或大于所有colB的场景做兜底（比如下界取colB最小值，上界取colB最大值），可以用COALESCE函数：

SELECT
    a.colA,
    COALESCE(
        (SELECT MAX(colB) FROM tableB WHERE colB < a.colA),
        (SELECT MIN(colB) FROM tableB)
    ) AS lower_bound,
    COALESCE(
        (SELECT MIN(colB) FROM tableB WHERE colB > a.colA),
        (SELECT MAX(colB) FROM tableB)
    ) AS upper_bound
FROM tableA a;

方式3：笛卡尔积+窗口函数（适合复杂扩展） #

如果需要更灵活的排序或筛选逻辑，可以通过笛卡尔积关联两表，再用窗口函数标记目标边界：

WITH combined_data AS (
    -- 筛选出小于colA的colB，并按降序排序标记
    SELECT
        a.colA,
        b.colB,
        ROW_NUMBER() OVER (PARTITION BY a.colA ORDER BY b.colB DESC) AS lower_rank
    FROM tableA a
    CROSS JOIN tableB b
    WHERE b.colB < a.colA
    
    UNION ALL
    
    -- 筛选出大于colA的colB，并按升序排序标记
    SELECT
        a.colA,
        b.colB,
        ROW_NUMBER() OVER (PARTITION BY a.colA ORDER BY b.colB ASC) AS upper_rank
    FROM tableA a
    CROSS JOIN tableB b
    WHERE b.colB > a.colA
)
SELECT
    colA,
    MAX(CASE WHEN lower_rank = 1 THEN colB END) AS lower_bound,
    MIN(CASE WHEN upper_rank = 1 THEN colB END) AS upper_bound
FROM combined_data
GROUP BY colA;

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