我需要创建一个视图
这是我到目前为止所拥有的:
SELECT Book.BookTitle
FROM Book, Borrower, Client
WHERE Book.BookId = Borrower.BookId AND Borrower.ClientId = Client.ClientId AND COUNT(Book.BookId) >
(SELECT COUNT(Client.ClientId) FROM Client)*0.2
GROUP BY Book.BookTitle
ORDER BY COUNT(Book.BookTitle) DESC;
我不断收到此错误:消息147,级别15,状态1,第547行聚合可能不会出现在WHERE子句中,除非它在HAVING子句或选择列表中包含的子查询中,并且正在聚合的列是外部参考。
我究竟做错了什么?
我认为此查询不会为您提供所需的信息,即“显示至少20%的客户借用的标题”,但我在语法上更正了您的查询。您不能在where
子句中使用聚合函数。Andgroup by
子句在查询中的位置不正确。
SELECT
Book.BookTitle, COUNT(Book.BookId)
FROM
Book
join Borrower
on Book.BookId = Borrower.BookId
join Client
on Borrower.ClientId = Client.ClientId
GROUP BY Book.BookTitle
having COUNT(Book.BookId) >
(SELECT COUNT(Client.ClientId) FROM Client)*0.2
ORDER BY COUNT(Book.BookTitle) DESC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句