如何为以下情况构造查询:

我有三个表:

  • 书籍(与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

    09-09 23:56