主页 / dba / 问题 / 77860
Accepted
Minu
Asked: 2014-09-28 09:58:15 +0800 CST

我如何获得此查询？数据库服务器

  • 772

我有一个数据库模式：

member(memb_no, name, age)  
book(isbn, title, authors, publisher)  
borrowed(memb_no, isbn, date)

这是问题：

对于每个出版商，打印出借过该出版商五本书以上的成员的姓名。

我该如何为此编写查询？

以下是我的尝试：

Select B.publisher,
M.memb_no, M.name  
From book as B,
member as M,
borrowed as R  
Where M.memb_no = R.memb_no and B.isbn = R.isbn and R.isbn in   
(select B.publisher, count (R.isbn)   
from borrowed as R and book as B   
where B.isbn = R.isbn   
group by B.publisher   
having count >5);

请指出错误并解释。

sql-server aggregate

2 个回答

  1. Best Answer

    试试这个：

    WITH  publisher_memb_cte (publisher,memb_no)
AS
(
SELECT b.publisher,r.memb_no
FROM book b
JOIN borrowed r ON b.isbn=r.isbn
group by b.publisher,r.memb_no
HAVING count (*)>5
)
SELECT cte.publisher, m.name
FROM publisher_memb_cte cte
JOIN member m on cte.memb_no = m.memb_no

    避免性能损失的提示：

    1. 尽可能避免在 where 子句中选择
    2. 在 where 子句中使用连接而不是“连接”

    您的代码中的语法错误位于：

    from borrowed as R and book as B

    应该是

    from borrowed as R, book as B
    • 1 
  2.     with book_borrowed(memb_no , isbn , publisher) as

    (  select memb_no , borrowed.isbn , publisher

       from

       book join borrowed on book.isbn = borrowed.isbn
    ) 


    select  name , publisher

    from book_borrowed join member on book_borrowed.memb_no = member.memb_no

    group by member.memb_no , publisher , name

    having count(isbn) > 5;
    • 0

相关问题