SQL Server中如何查询同列多区间最大值并生成独立列存储

假设你的表名为stock_data，可替换为实际使用的表名，以下两种方案可直接适配需求：

方案1：仅返回各区间最大值（1行结果） #

使用条件聚合实现，兼容所有SQL Server版本：

SELECT
    MAX(CASE WHEN id BETWEEN 1 AND 14 THEN hi END) AS max_hi_1_14,
    MAX(CASE WHEN id BETWEEN 1 AND 15 THEN hi END) AS max_hi_1_15,
    MAX(CASE WHEN id BETWEEN 3 AND 16 THEN hi END) AS max_hi_3_16,
    MAX(CASE WHEN id BETWEEN 4 AND 17 THEN hi END) AS max_hi_4_17,
    MAX(CASE WHEN id BETWEEN 5 AND 18 THEN hi END) AS max_hi_5_18,
    MAX(CASE WHEN id BETWEEN 6 AND 19 THEN hi END) AS max_hi_6_19
FROM 你的实际表名;

方案2：保留原表所有行，每行附带各区间最大值 #

使用窗口函数实现，适用于SQL Server 2012及以上版本：

SELECT
    *,
    MAX(CASE WHEN id BETWEEN 1 AND 14 THEN hi END) OVER() AS max_hi_1_14,
    MAX(CASE WHEN id BETWEEN 1 AND 15 THEN hi END) OVER() AS max_hi_1_15,
    MAX(CASE WHEN id BETWEEN 3 AND 16 THEN hi END) OVER() AS max_hi_3_16,
    MAX(CASE WHEN id BETWEEN 4 AND 17 THEN hi END) OVER() AS max_hi_4_17,
    MAX(CASE WHEN id BETWEEN 5 AND 18 THEN hi END) OVER() AS max_hi_5_18,
    MAX(CASE WHEN id BETWEEN 6 AND 19 THEN hi END) OVER() AS max_hi_6_19
FROM 你的实际表名;

逻辑说明 #

• CASE WHEN会先筛选对应id区间的hi值，不在区间的返回NULL，MAX聚合会自动忽略NULL值，最终得到对应区间的最大值
• 代码运行前需将你的实际表名替换为自己的表名

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