SQL中如何实现同Asbesto Survey id分组取最低值填充行级状态

需求说明 #

同一Asbesto Survey id关联多条Asbestos Status Lines记录时，取该ID分组下Asbestos Status的最低值，填充到组内所有行的行级Asbestos Status字段。
之前用left join搭配case语句未得到预期结果，通常是子查询聚合逻辑错误、关联条件不匹配导致，以下是可直接复用的SQL实现方案：

方案1：窗口函数写法（推荐，适配MySQL8.0+、PostgreSQL、SQL Server、Hive、Spark等所有支持窗口函数的SQL引擎） #

写法最简洁，不需要额外关联，性能更好：

SELECT
  *,
  -- 按Survey id分区，取每个分区内状态的最小值
  MIN(`Asbestos Status`) OVER (PARTITION BY `Asbesto Survey id`) AS row_level_asbestos_status
FROM 你的业务表名;

方案2：聚合关联写法（适配不支持窗口函数的老版本SQL环境，如MySQL5.x） #

注意关联时仅需匹配Asbesto Survey id即可，不需要额外加冗余case判断：

SELECT
  t1.*,
  t2.min_status AS row_level_asbestos_status
FROM 你的业务表名 t1
LEFT JOIN (
  SELECT
    `Asbesto Survey id`,
    MIN(`Asbestos Status`) AS min_status
  FROM 你的业务表名
  GROUP BY `Asbesto Survey id`
) t2
ON t1.`Asbesto Survey id` = t2.`Asbesto Survey id`;

特殊场景注意 #

如果Asbestos Status是自定义顺序的文本枚举值（比如按风险等级排序：高风险>中风险>低风险>无风险），直接用MIN会按字符序排序不符合业务规则，需要先做权重映射再取最小值，示例：

SELECT
  *,
  -- 先把文本转成对应权重数字，取最小权重后再映射回原文本
  CASE MIN(
    CASE `Asbestos Status`
      WHEN '高风险' THEN 1
      WHEN '中风险' THEN 2
      WHEN '低风险' THEN 3
      WHEN '无风险' THEN 4
    END
  ) OVER (PARTITION BY `Asbesto Survey id`)
    WHEN 1 THEN '高风险'
    WHEN 2 THEN '中风险'
    WHEN 3 THEN '低风险'
    WHEN 4 THEN '无风险'
  END AS row_level_asbestos_status
FROM 你的业务表名;

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