如何为以下情况构造查询:
我有三个表:
peopleserno
上的人链接)peopleserno
上的人链接)我想创建一个SQL,输出将给出一行,其中包含特定人已阅读/观看了多少本书和视频。
输出示例:
John | 3 (books) | 2 (videos)
我已经尝试过类似的方法,但是它不起作用:
select a.name,
count(b.serno) as books,
count(c.serno) as videos
from people a,
books b,
videos c
where a.serno = b.peopleserno
and a.serno = c.peopleserno
谢谢。
最佳答案
您需要left join
才能获得甚至没有阅读/观看任何内容的用户,然后需要group by
来获得特定的用户数
select a.name,
count(distinct b.title) as books,
count(distinct c.title) as videos
from people a
left join books b on a.serno = b.peopleserno
left join videos c on a.serno = c.peopleserno
group by a.name
SQLFiddle demo