mysql - 如何在单个查询中从多个表中获取数据

Question

我有以下表格

包含列名称(UserId、姓名、电子邮件)的用户表

带有列名(UserId、VideoLink)的视频表

如果 status=0 喜欢，如果 status=1 不喜欢，则包含列名 (UserId,Status) 的 Activity 表

我想得到如下结果

姓名、Total_Videos_posted_by_that_user、count(Likes)、count(dislikes)

如何在单个查询中查询它们？

Answer 1

select u.name, 
       videocount, 
       dislikes,
       likes
from user u
left join 
(
  select u.name, count(v.videolink) as videocount
  from user u
  inner join videos v on v.userid = u.userid
) x on x.name = u.name
left join 
(
  select u.name, 
         sum(a.status=1) as dislikes,
         sum(a.status=0) as likes
  from user u
  inner join activity a on a.userid = u.userid
) y on y.name = u.name

SQLFiddle demo