MySQL按职业计算平均借书量的查询报错问题求助

你的思路方向是对的，但两次尝试都存在逻辑问题：

• 第一次的子查询只计算了每个用户的借书总数，但没有保留用户的ClientID或职业信息，外层查询根本无法获取client.Occupation，因此报错。
• 第二次的写法逻辑混乱，将聚合子查询放在WHERE条件里不符合SQL语法，且BorrowIDCount并未定义在主查询的字段列表中。

方法一：先统计用户借书数，再关联职业求平均 #

先通过子查询算出每个用户的总借书量并保留ClientID，再与client表关联获取职业信息，最后按职业分组计算平均值：

SELECT c.Occupation, AVG(user_borrow.total_borrows) AS avg_borrows_per_occupation
FROM client c
JOIN (
    SELECT ClientID, COUNT(BorrowID) AS total_borrows
    FROM borrower
    GROUP BY ClientID
) user_borrow ON c.ClientID = user_borrow.ClientID
GROUP BY c.Occupation;

这里用JOIN替代了旧的逗号连接表写法，逻辑更清晰。子查询仅从borrower表统计数据，再关联client表获取职业，避免了不必要的表连接操作。

方法二：窗口函数聚合（MySQL 8.0+适用） #

如果你的MySQL版本支持窗口函数，可直接在一次查询中完成用户借书数统计和职业平均值计算：

SELECT DISTINCT
    c.Occupation,
    AVG(COUNT(b.BorrowID)) OVER (PARTITION BY c.Occupation) AS avg_borrows_per_occupation
FROM client c
JOIN borrower b ON c.ClientID = b.ClientID
GROUP BY c.ClientID, c.Occupation;

COUNT(b.BorrowID)先计算每个用户的借书数，AVG(...) OVER (PARTITION BY c.Occupation)再对同职业用户的借书数求平均，DISTINCT用于去重，确保每个职业仅显示一行平均值结果。

补充：包含未借书用户的统计 #

如果需要将从未借过书的用户（借书数为0）纳入平均值计算，需使用LEFT JOIN结合COALESCE函数处理NULL值：

SELECT c.Occupation, AVG(COALESCE(user_borrow.total_borrows, 0)) AS avg_borrows_per_occupation
FROM client c
LEFT JOIN (
    SELECT ClientID, COUNT(BorrowID) AS total_borrows
    FROM borrower
    GROUP BY ClientID
) user_borrow ON c.ClientID = user_borrow.ClientID
GROUP BY c.Occupation;

COALESCE会将未借书用户的total_borrows（NULL）替换为0，确保平均值计算包含这类用户。

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