列 'Book.Title' 在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中

Question

我正在创建一个图书馆管理系统，我想在数据库中显示每本书的ISBN、书名和副本编号以及它们所在的图书馆。

这是我尝试过的代码：

CREATE VIEW library_information 
AS  
SELECT b.ISBN, b.Title, COUNT(CopyNumber) AS NoOfCopies, l.Name AS Location,  l.MainName AS MainBranch
FROM Book b
INNER JOIN Copy c ON c.CBookISBN = b.ISBN 
INNER JOIN Branch l ON l.Name = c.LibraryName
GROUP BY b.ISBN;

它给出了一个错误列"Book.Title"在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中

解决这个问题的一些技巧？

Answer 1

一个简单的规则：不能在不属于GROUP BY的SELECT子句中使用没有任何聚合的实际列。

所以在你的查询中，它应该是：

CREATE VIEW library_information 
AS  
SELECT b.ISBN, -- unaggregated column - must be in GROUP BY clause
b.Title, -- unaggregated column - must be in GROUP BY clause
COUNT(CopyNumber) AS NoOfCopies, 
l.Name AS Location, -- unaggregated column - must be in GROUP BY clause
l.MainName AS MainBranch -- unaggregated column - must be in GROUP BY clause
FROM Book b
INNER JOIN Copy c ON c.CBookISBN = b.ISBN 
INNER JOIN Branch l ON l.Name = c.LibraryName
GROUP BY b.ISBN,
b.Title, -- added this and following columns in GROUP BY
l.Name,
l.MainName;