有如下基本表:
要求完成如下查询:
(1),列出当前剩余书名及数量
(2),列出所有书名及当前剩余数量
解:
(1):
SELECT title, count(title) AS available
FROM book
WHERE id not in
(select bookid
from borrow)
GROUP BY title;
查询结果:
(2):
SELECT title, count(id)-count(userid) AS remain
FROM book LEFT JOIN borrow ON book.id=borrow.bookid
GROUP BY title;
查询结果:
可见,与(1)相比的区别是:将无剩余的书名也列出来了。
注:
1,(2)中用了左外连接,其效果是在连接时保持左边不丢失条目,如:
SELECT *
FROM book LEFT JOIN borrow ON book.id=borrow.bookid;
查询结果:
2,count(userid)不计数空值。