[SQL ] 如何在同一table中找出相同的資料
請問大家, 若我現有一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
推
10/16 14:30, , 1F
10/16 14:30, 1F
→
10/16 23:14, , 2F
10/16 23:14, 2F
討論串 (同標題文章)
以下文章回應了本文：
完整討論串 (本文為第 1 之 2 篇)：