MySQL如何仅返回分组查询结果集中平均值最低的记录

首先先修正你原SQL里的两处语法问题，否则语句无法正常执行：

1. 时间字段名不统一：首行写的是拼写错误的recieved_on（正确拼写为received_on），后续筛选、分组条件里写的是带空格的received on，需要统一替换为你表中实际的时间字段名。
2. 字段名如果真的包含空格，需要用反引号或者方括号包裹，否则会被判定为语法错误。

要获取average值最低的单条记录，核心逻辑是对分组统计后的日均值结果按average升序排序，仅返回第一条即可，不同数据库的语法略有区别：

• MySQL/PostgreSQL/SQLite 等支持LIMIT的数据库，直接在原语句末尾加排序和行数限制：

SELECT 
  DATE(received_on) AS Day, 
  ROUND(COUNT(*) / 24) AS average
FROM message
WHERE facility IN ('FACID')
  AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
GROUP BY DATE(received_on)
ORDER BY average ASC
LIMIT 1;

• SQL Server/Access 用TOP关键字取首行：

SELECT TOP 1
  DATE(received_on) AS Day, 
  ROUND(COUNT(*) / 24) AS average
FROM message
WHERE facility IN ('FACID')
  AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
GROUP BY DATE(received_on)
ORDER BY average ASC;

• Oracle 12c及以上版本用FETCH FIRST语法：

SELECT 
  DATE(received_on) AS Day, 
  ROUND(COUNT(*) / 24) AS average
FROM message
WHERE facility IN ('FACID')
  AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
GROUP BY DATE(received_on)
ORDER BY average ASC
FETCH FIRST 1 ROW ONLY;

特殊场景说明：如果存在多天的average值并列最低的情况，上述写法只会返回排序后排在最前面的1条记录。如果你需要把所有并列最低的记录都查出来，可以用子查询先算出最小均值，再做匹配：

WITH daily_stat AS (
  SELECT 
    DATE(received_on) AS Day, 
    ROUND(COUNT(*) / 24) AS average
  FROM message
  WHERE facility IN ('FACID')
    AND received_on BETWEEN '2022-05-29 00:00:00' AND '2022-06-04 23:59:59'
  GROUP BY DATE(received_on)
)
SELECT Day, average FROM daily_stat
WHERE average = (SELECT MIN(average) FROM daily_stat);

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