作者cytogenous (有希望? 希望有!)
看板Database
标题[SQL ] 如何在同一table中找出相同的资料
时间Thu Oct 16 09:12:11 2008
请问大家, 若我现有一table的schema如下
Books(Library_name: string, ISBN: string, Book_name: string)
(无PRIMARY KEY 的限制)
而我想找出这其中每个图书馆都有的书, 且回传每间图书馆的本数
L_NAME ISBN B_NAME BOOKCOUNT
------------- -------------------- ---------------- -----------
LIBRARY A 0-12345-678-3 DM BOOK 1
LIBRARY A 0-12345-678-9 DB BOOK 2
LIBRARY B 0-12345-678-3 DB BOOK 2
LIBRARY B 1-12345-678-6 DM BOOK 1
我本来的写法:
SELECT LIB1.L_NAME, LIB1.ISBN, LIB1.B_NAME, COUNT (*) BOOKCOUNT
FROM BOOKS LIB1
WHERE LIB1.L_NAME = 'A'
GROUP BY LIB1.L_NAME, LIB1.ISBN, LIB1.B_NAME
HAVING EXISTS (
SELECT LIB2.L_NAME, LIB2.ISBN, LIB2.B_NAME, COUNT (*)
FROM BOOKS LIB2
WHERE LIB2.L_NAME = 'B'
GROUP BY LIB2.L_NAME, LIB2.ISBN, LIB2.B_NAME
HAVING LIB1.ISBN = LIB2.ISBN AND LIB1.B_NAME = LIB2.B_NAME );
但这样只能回传LIBRARY A的书及数量而已,
请问有更好的SQL语法吗? 谢谢!
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 67.171.67.160
1F：推 tactical:你的ISBN是可以重复用的？(第一行跟第三行) 10/16 14:30
2F：→ cytogenous:是的 无PRIMARY KEY CONSTRAINT 10/16 23:14