从多个表中选择并使用GROUP BY进行分组

Question

我在进行多表查询时遇到了一些问题（关系型数据库：Access）。

这是数据库模式：（仅在此查询中使用 S_Cards、Books、Authors、Students 表） S_Cards 是指学生在图书馆预订的书籍。

查询： 选出最受学生欢迎的作者并计算该作者在图书馆预定的数量。

虽然我可以在一次查询中得到订单和作者列表，如下所示：

SELECT 
    Students.FirstName & " " & Students.LastName AS [Student], 
    Books.Name AS [Book], Authors.FirstName & " " & Authors.LastName AS [Author]
FROM 
    Students, 
    Books, 
    S_Cards, 
    Authors
WHERE 
    S_Cards.ID_Student = Students.ID 
AND S_Cards.ID_Book = Books.ID 
AND Books.ID_Author = Authors.ID
ORDER BY Authors.LastName

结果（抱歉，是俄语）：

我无法理解为什么不能像这样使用COUNT和GROUP BY：

SELECT 
    Students.FirstName & " " & Students.LastName AS [Student], 
    Books.Name AS [Book], 
    COUNT(Authors.FirstName & " " & Authors.LastName) AS [Number of books]
FROM Students, Books, S_Cards, Authors
WHERE 
    S_Cards.ID_Student = Students.ID 
AND S_Cards.ID_Book = Books.ID 
AND Books.ID_Author = Authors.ID
GROUP BY 3

我收到一个错误提示，指出 'Authors.FirstName & " " & Authors.LastName' 不是静态函数或组的一部分。

问题：

1. 有没有办法在不使用 JOIN 的情况下执行此查询，只使用 GROUP BY、SELECT、UNION？如何实现？
2. 我的第二个查询有什么问题？

- Aremyst

Answer 1

解决方案（摘自问题中的旧版本）：

SELECT TOP 1 Author, COUNT(Book) AS [Number of books] FROM
(
    SELECT 
        Students.FirstName & " " & Students.LastName AS [Student], 
        Books.Name AS [Book], 
        Authors.FirstName & " " & Authors.LastName AS [Author]
    FROM 
        Students, 
        Books, 
        S_Cards, 
        Authors
    WHERE 
        S_Cards.ID_Student = Students.ID AND
        S_Cards.ID_Book = Books.ID AND
        Books.ID_Author = Authors.ID
    ORDER BY Authors.LastName
)
GROUP BY Author
ORDER BY 2 DESC

- Pang

Answer 2

在SQL Server中，字符串连接运算符是+而不是&。此外，您应该按非聚合函数的内容进行分组。

SELECT Students.FirstName + ' ' + Students.LastName AS [Student]
     , Books.Name AS [Book]
     , COUNT(Authors.FirstName + ' ' + Authors.LastName) AS [Number of books]
  FROM Students
  JOIN S_Cards
    ON S_Cards.ID_Student = Students.ID
  JOIN BOOKS
    ON S_Cards.ID_Book = Books.ID 
  JOIN Authors
    ON Books.ID_Author = Authors.ID
 GROUP BY Students.FirstName + ' ' + Students.LastName
        , Books.Name

请注意，我已将您的查询更改为标准的ANSI连接语法，这使得错误更难出现，并且更容易阅读。

思考一下，您的计数似乎有点奇怪。书的数量不是 COUNT(Books.ID) 吗？

- Ben

---

这里假设你正在使用 SQL Server（看起来是这样）。请在以后始终使用适当的 RDBMS 标记问题。 - Ben

谢谢回复。 我认为COUNT（Books.ID）会计算某本书被订购的次数。我需要计算任何作者的书被订购的次数，而不是某个特定的书。RDBMS是Access。 - Aremyst

Answer 3

你必须对任何不属于聚合函数的内容进行分组：

SELECT 
    Students.FirstName & " " & Students.LastName AS [Student], 
    Books.Name AS [Book], 
    COUNT(Authors.FirstName & " " & Authors.LastName) AS [Number of books]
FROM Students, Books, S_Cards, Authors
WHERE 
    S_Cards.ID_Student = Students.ID 
AND S_Cards.ID_Book = Books.ID 
AND Books.ID_Author = Authors.ID
GROUP BY Students.FirstName & " " & Students.LastName, 
    Books.Name AS [Book]

我建议您开始使用显式连接而不是隐式连接。对于大多数情况来说，它们是 MS Access 的更好选择。

<...>
FROM Students
INNER JOIN S_Cards
ON Students.ID = S_Cards.ID_Student

或者

<...>
FROM Students
LEFT JOIN S_Cards
ON Students.ID = S_Cards.ID_Student

查询设计窗口将允许您使用正确的语法构建联接。只需从一张表拖放连接字段到另一张表并选择所需的连接类型即可。

- Fionnuala