我有两个名为“书”和“作者”的表。“ Book”表的属性为:PK- Book_Num,Book_Title,Book_Year,Book_Cost,Book_Subject。“作者”表的属性为PK- Au_ID,Au_Fname,Au_Lname。这两个表彼此之间具有M:N关系,并且它们之间的桥表名为“写入”,并且它具有由Book_Num和Au_ID组成的两个表的复合主键。这是我的问题:我想为所有从未编写过主题为“编程”的书的作者编写查询以显示Au_ID,Au_Fname和Au_Lname,并按Au_Lname排序结果。我将如何使用子查询执行此操作?
试一下:
SELECT A.Au_ID, A.Au_Fname, A.Au_Lname FROM Author A
JOIN Writes W ON A.Au_ID = W.Au_ID
JOIN Book B ON B.Book_Num = W.Book_Num
WHERE A.Au_ID NOT IN (
SELECT A.Au_ID FROM Author A
JOIN Writes W ON A.Au_ID = W.Au_ID
JOIN Book B ON B.Book_Num = W.Book_Num
WHERE B.Book_Subject = 'Programming'
)
ORDER BY A.Au_Lname ASC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句