我在 SQL 中的"group by"代码有什么问题？

Question

反馈表：

CREATE TABLE Feedback 
(
FeedbackID nvarchar(50) NOT NULL PRIMARY KEY, 
Rating SMALLINT, 
Comments nvarchar(50), 
MemberID nvarchar(50) FOREIGN KEY REFERENCES Member(MemberID), 
BookID NVARCHAR(50) FOREIGN KEY REFERENCES Book(BookID)
); 

BOOK表：

CREATE TABLE Book  
(
BookID nvarchar(50) NOT NULL PRIMARY KEY, 
Name nvarchar(50), 
SerialNumber nvarchar(50), 
CategoryID nvarchar(50) FOREIGN KEY REFERENCES Category(CategoryID), 
PublisherID NVARCHAR(50) FOREIGN KEY REFERENCES Publisher(PublisherID), 
Unit_Price nvarchar(50), 
Quantity NVARCHAR (50)
); 

我只是通过使用才得到这个

SELECT BookID, COUNT(FeedbackID) AS Numoffeedback 
FROM Feedback 
GROUP BY BookID;
BookID | TotalNumberofFeedbacks
-------+-----------------------

但我想要的输出是：

BookID | Name | TotalNumberofFeedbacks
-------+------+-----------------------

我试过做这个

SELECT BookID, COUNT(FeedbackID) AS Numoffeedback 
FROM Feedback 
GROUP BY BookID, Name; 

但是我收到了这个错误"；列名"name"无效。

Answer 1

您需要将两个表连接在一起，然后按book进行聚合。您可以尝试以下查询：

SELECT b.BookID, b.Name, COUNT(f.BookID) AS TotalNumberOfFeedbacks
FROM Book b
LEFT JOIN Feedback f
ON f.BookID = b.BookID
GROUP BY b.BookID, b.Name;

注意，我们在这里计算f.BookID。原因是如果某本书没有反馈，我们希望计数为零。