SQL如何不用循环通过单条语句交换阈值类型对应的成对行数据

可以仅用单条SQL完成上述更新操作，无需使用循环，以下是两种适配不同场景的方案：

方案1：通用兼容方案（适配MySQL、PostgreSQL、SQL Server等绝大多数数据库） #

利用聚合函数直接按新的阈值规则赋值，不需要依赖同表数据关联，逻辑更稳妥：

-- 请将语句中的device_threshold替换为你实际的表名
UPDATE device_threshold t
SET ThresholdValue = CASE 
  WHEN ThresholdType = 0 THEN (SELECT MAX(ThresholdValue) FROM device_threshold WHERE DeviceID = t.DeviceID)
  WHEN ThresholdType = 1 THEN (SELECT MIN(ThresholdValue) FROM device_threshold WHERE DeviceID = t.DeviceID)
END;

逻辑说明：调整规则后0代表最大阈值、1代表最小阈值，直接给对应类型赋值为同设备下的最大值/最小值即可得到目标结果。

方案2：自连接交换方案（适合数据完全符合每个设备仅存0、1两类阈值的场景） #

通过同表自关联直接交换同设备下两个阈值的数值，执行效率更高：

-- 请将语句中的device_threshold替换为你实际的表名，MySQL环境下可直接执行
UPDATE device_threshold t1
JOIN device_threshold t2 
  ON t1.DeviceID = t2.DeviceID 
  AND t1.ThresholdType <> t2.ThresholdType
SET t1.ThresholdValue = t2.ThresholdValue;

注意：执行更新操作前建议先运行查询语句验证结果是否符合预期，避免误操作导致数据异常，验证语句参考：

SELECT 
  DeviceID,
  ThresholdType,
  ThresholdValue AS OldValue,
  CASE 
    WHEN ThresholdType = 0 THEN MAX(ThresholdValue) OVER (PARTITION BY DeviceID)
    WHEN ThresholdType = 1 THEN MIN(ThresholdValue) OVER (PARTITION BY DeviceID)
  END AS ExpectedNewValue
FROM device_threshold;

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