MySQL 循环和多个 LEFT 连接

Question

我得到了以下代码:

SELECT 
      COALESCE(rv.views, 0) as views
   FROM 
      ( select 0 as n 
        union all select 1 
        union all select 2 
        union all select 3 ) n 
        LEFT JOIN restaurant_views rv 
           on rv.date = date_add("2015-02-24", interval - n.n day) 
           and restaurant_id = 192

此代码为我提供了一家餐厅过去 4 天的浏览量。
我正在寻找类似的查询来获取一家餐厅过去 4 天的点赞数。

这是我到目前为止得到的:

SELECT 
      ( COUNT( DISTINCT a.restaurant_id) 
      + COUNT( DISTINCT d.restaurant_id)) as num_likes
   FROM 
      ( select 0 as n 
        union all select 1 
        union all select 2 
        union all select 3 ) n 
         LEFT JOIN apple_likes a 
            on a.vote_date = date_add("2015-02-24", interval - n.n day) 
            and a.restaurant_id = 192
         LEFT JOIN android_likes d 
            on d.vote_date = date_add("2015-02-24", interval - n.n day) 
            and d.restaurant_id = 192

这是输出，正如您所看到的，这不是我正在寻找的内容:

我需要更改什么才能获取最后一个查询中的点赞数？
(我已经检查过该餐厅全天都有点赞，所以我确信查询有问题)

Answer 1

试试这个:

SELECT 
      ( a.likes) 
      + d.likes) as num_likes
   FROM 
      ( select 0 as n 
        union all select 1 
        union all select 2 
        union all select 3 ) n 
         LEFT JOIN (
            SELECT vote_date,COUNT(*) as likes 
            FROM apple_likes
            WHERE restaurant_id = 192
            GROUP BY restaurant_id, vote_date
         ) as a 
            on a.vote_date = date_add("2015-02-24", interval - n.n day) 
         LEFT JOIN (
            SELECT vote_date, COUNT(*) as likes 
            FROM android_likes 
            WHERE restaurant_id = 192
            GROUP BY restaurant_id, vote_date
         ) as d 
            on d.vote_date = date_add("2015-02-24", interval - n.n day)