MySQL中正确计算limit_exceded值及统计最值与超限数量

首先咱们得先揪出你当前查询的核心问题：你写的CASE语句逻辑和你的实际需求完全搞反了！

你想要的是当myvalues.value小于40或者大于180时，limit_exceded设为1，但原代码里的条件是myvalues.value > sub_types.low_value and myvalues.value < sub_types.high_value then 1——这意味着只有当value落在low_value和high_value之间时才标记为1，和你的需求正好相反，这就是为什么所有结果都是0的原因。

修正后的查询方案 #

我给你分两种场景提供解决方案：

场景1：需要明细记录+全局统计值

如果你既要看到每条符合条件的记录详情，又要同时获取这批数据的最大值、最小值、异常记录数（limit_exceded=1的数量），可以用窗口函数实现：

SELECT 
  myvalues.`value`,
  sub_types.low_value,
  sub_types.high_value,
  -- 修正后的limit_exceded逻辑：小于40或大于180标记为1
  CASE WHEN myvalues.`value` < 40 OR myvalues.`value` > 180 THEN 1 ELSE 0 END AS limit_exceded,
  -- 全局最大值
  MAX(myvalues.`value`) OVER() AS max_value,
  -- 全局最小值
  MIN(myvalues.`value`) OVER() AS min_value,
  -- 全局异常记录总数
  SUM(CASE WHEN myvalues.`value` < 40 OR myvalues.`value` > 180 THEN 1 ELSE 0 END) OVER() AS exceeded_count
FROM myvalues
JOIN sub_types ON myvalues.sub_type_id = sub_types.id
WHERE myvalues.sub_type_id = '68'
  AND myvalues.observation_id IN('455', '471', '470', '469', '468', '467', '466', '465', '462', '461', '460', '459', '458', '457', '456', '372', '453', '373', '376', '439', '440', '441', '442', '443', '445', '446', '447', '448', '452', '454');

场景2：只需要聚合统计结果

如果你不需要明细，只关心这批数据的最大值、最小值和异常记录数，可以直接用聚合函数：

SELECT 
  MAX(myvalues.`value`) AS max_value,
  MIN(myvalues.`value`) AS min_value,
  SUM(CASE WHEN myvalues.`value` < 40 OR myvalues.`value` > 180 THEN 1 ELSE 0 END) AS exceeded_count
FROM myvalues
JOIN sub_types ON myvalues.sub_type_id = sub_types.id
WHERE myvalues.sub_type_id = '68'
  AND myvalues.observation_id IN('455', '471', '470', '469', '468', '467', '466', '465', '462', '461', '460', '459', '458', '457', '456', '372', '453', '373', '376', '439', '440', '441', '442', '443', '445', '446', '447', '448', '452', '454');

额外注意事项 #

如果你的myvalues.value字段是字符串类型（而非数值型），一定要先把它转成数值再比较，否则字符串比较会出现错误（比如'9'字符串会被判定为大于'40'，因为首字符'9'的ASCII值比'4'大）。转成数值的写法可以是：

CASE WHEN CAST(myvalues.`value` AS DECIMAL) < 40 OR CAST(myvalues.`value` AS DECIMAL) > 180 THEN 1 ELSE 0 END

这样修改后，像900、9这类符合条件的值就会被正确标记为limit_exceded=1了。

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