我想获得有 2 位作者的书的详细信息,为此我编写了以下查询。
SELECT BOOK.Book_Title
, PUBLISHER.Publisher_Name
, BOOK.Book_Type
, AUTHOR.Author_Last
, AUTHOR.Author_First
FROM AUTHOR
, WROTE
, BOOK
, PUBLISHER
WHERE AUTHOR.Author_Num = WROTE.Author_Num
AND WROTE.Book_Code = BOOK.Book_Code
AND BOOK.Publisher_Code = PUBLISHER.Publisher_Code
HAVING COUNT(WROTE.Book_Code) = 2;
但是当我运行它时,我返回了 0 行。尽管数据在表中可用,并且有几行有 2 位作者针对一本书。
这是我应用查询的架构。
以下是 WROTE 表中可用的数据,它是 BOOK 和 AUTHOR 表的多对多关系。
请告诉我问题出在哪里?
您的查询不正确,因为当您尝试应用假定具有某些分组字段的 HAVING 条件时,它没有 GROUP BY 部分。
我想使用以下查询(MS SQL):
SELECT bk.Book_Title
, pb.Publisher_Name
, bk.Book_Type
, auth.Author_Last
, auth.Author_First
FROM AUTHOR as auth
join WROTE as wr on auth.Author_Num = wr.Author_Num
join BOOK as bk on wr.Book_Code = bk.Book_Code
join PUBLISHER as pb on bk.Publisher_Code = pb.Publisher_Code
where exists (SELECT 1
from WROTE as wr_ex
where wr_ex.Book_Code = wr.Book_Code
group by wr_ex.Book_Code
having count(1) = 2 )
子查询可能有一个变体,在 FROM 部分中选择 Book_codes 和 2 个作者,然后加入所有其他内容
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句