SQL如何按姓名首字母分组统计平均身高及补全全字母对应值

1. 按姓名首字母分组计算平均身高 #

不需要手动遍历，直接用SQL内置的字符串截取函数提取首字母分组计算即可，逻辑非常简洁，新增不同首字母的记录会自动扩展结果，无需修改查询代码。

不同数据库的写法差异仅在首字母提取函数：

• MySQL/MariaDB 写法：

SELECT
  LEFT(Name, 1) AS 首字母,
  ROUND(AVG(Height), 2) AS 平均身高 -- ROUND可按需调整保留的小数位数
FROM 人员表名
GROUP BY LEFT(Name, 1)
ORDER BY 首字母;

• PostgreSQL/SQLite/Oracle 写法：

SELECT
  SUBSTR(Name, 1, 1) AS 首字母,
  ROUND(AVG(Height), 2) AS 平均身高
FROM 人员表名
GROUP BY SUBSTR(Name, 1, 1)
ORDER BY 首字母;

如果存在姓名大小写不统一的情况，可以在提取首字母时加UPPER()/LOWER()函数统一格式，避免同一字母大小写分成两组的问题。

2. 输出覆盖全部26个英文字母的结果 #

需要先生成包含A-Z所有字母的临时表，再和人员表左关联，无匹配记录的首字母用COALESCE将空值转为0即可：

支持递归CTE的数据库（MySQL8.0+/PostgreSQL/SQLite3.8.3+）写法： #

WITH RECURSIVE 字母表 AS (
  SELECT 'A' AS 字母
  UNION ALL
  SELECT CHAR(ORD(字母) + 1) FROM 字母表 WHERE 字母 < 'Z'
)
SELECT
  a.字母 AS 首字母,
  COALESCE(ROUND(AVG(b.Height), 2), 0) AS 平均身高
FROM 字母表 a
LEFT JOIN 人员表名 b ON LEFT(b.Name, 1) = a.字母 -- 其他数据库替换为SUBSTR即可
GROUP BY a.字母
ORDER BY a.字母;

不支持递归CTE的低版本数据库写法： #

手动构造26个字母的临时表即可，逻辑完全一致：

SELECT
  a.字母 AS 首字母,
  COALESCE(ROUND(AVG(b.Height), 2), 0) AS 平均身高
FROM (
  SELECT 'A' AS 字母 UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL
  SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' UNION ALL
  SELECT 'G' UNION ALL SELECT 'H' UNION ALL SELECT 'I' UNION ALL
  SELECT 'J' UNION ALL SELECT 'K' UNION ALL SELECT 'L' UNION ALL
  SELECT 'M' UNION ALL SELECT 'N' UNION ALL SELECT 'O' UNION ALL
  SELECT 'P' UNION ALL SELECT 'Q' UNION ALL SELECT 'R' UNION ALL
  SELECT 'S' UNION ALL SELECT 'T' UNION ALL SELECT 'U' UNION ALL
  SELECT 'V' UNION ALL SELECT 'W' UNION ALL SELECT 'X' UNION ALL
  SELECT 'Y' UNION ALL SELECT 'Z'
) a
LEFT JOIN 人员表名 b ON LEFT(b.Name, 1) = a.字母
GROUP BY a.字母
ORDER BY a.字母;

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