试图平均分组计数的结果?

Question

我正在使用MySQL，并试图创建一个查询来解决这个问题:

按职业划分的平均借阅书数

我的计划是计算'BorrowID'实例的数量，因为每次借书时它都会创建一个唯一的BorrowID。然后按clientID对这些图书进行分组，以便每个人都有他们所借阅的图书总数。然后这就是我开始迷路的地方，因为我显然想平均所有分组的职业，但我不确定我是否在这样做…

第一次尝试:

SELECT client.Occupation, AVG(BorrowIDCount)
FROM
(
SELECT COUNT(BorrowID) as BorrowIDCount
FROM client, borrower
WHERE client.ClientID = borrower.ClientID
GROUP BY borrower.ClientID
) as x
GROUP BY Occupation

但是它给出了错误:

Unknown column 'client.Occupation' in 'field list'

我认为这是因为外部查询需要知道哪些表…

于是我试着:

SELECT client.Occupation, AVG(BorrowIDCount)
FROM client, borrower
WHERE client.ClientID = borrower.ClientID AND
(
SELECT COUNT(BorrowID) as BorrowIDCount
FROM client, borrower
WHERE client.ClientID = borrower.ClientID
GROUP BY borrower.ClientID
)
GROUP BY Occupation

它不喜欢子查询的别名，所以我删除了它，虽然不知道为什么，但是它然后给出了这个错误:

Unknown column 'BorrowIDCount' in 'field list'

我觉得我可能在如何创建这个查询方面完全偏离了基础，但我也觉得我可能很接近，只是不理解这里的一些规则或语法。如果您能提供正确的帮助，我将不胜感激。

谢谢!

Answer 1

在我看来，你想要算出客户借的书的数量，然后按职业平均这个数字。让我们分步骤来做:

第一个是子查询，用于获取每个客户端的图书。

SELECT COUNT(*) borrowed, ClientID
FROM borrower
GROUP BY ClientID

接下来，我们在外部查询中使用该子查询来获得所需的平均值。

SELECT AVG(byclient.borrowed) average_borrowed,
client.Occupation
FROM (
SELECT COUNT(*) borrowed, ClientID
FROM borrower
GROUP BY ClientID
) byclient
LEFT JOIN client ON byclient.ClientID = client.ClientID
GROUP BY client.Occupation
ORDER BY AVG(byclient.borrowed) DESC, client.Occupation;

您的需求调用聚合的聚合，因此您必须将一个聚合查询嵌套在另一个聚合查询中。

LEFT JOIN允许包含没有任何占用的客户端。如果您不希望这样，只需使用JOIN。

你的问题中的第一个查询失败，因为你的FROM子句引用了一个缺少Occupation列的子查询(一个虚拟表)。第二个失败了，因为AND (virtual table)在SQL中没有任何意义。

虚表的嵌套是SS结构化部分tructuredQL 语言。