MySQL Join 2 个表和 SUM 收入 - 将两个查询合并为一个

Question

我有两张 table 。我需要获取过去 30 天的总收入，其中日期和 user_id 相同。

我需要将这些查询组合成一个 JOIN SQL 查询:

select * from user_revenue where user_id=$id AND `cdate` >= DATE(NOW() - INTERVAL 30 DAY)
select * from user_revenue_publisher where user_id=$id AND `cdate` >= DATE(NOW() - INTERVAL 30 DAY)

表:user_revenue

USER_ID                CDATE             REVENUE
1                      2019-03-15        15
2                      2019-03-15        18
5                      2019-03-15        29
9                      2019-03-15        11

表:user_revenue_publisher

USER_ID                CDATE             REVENUE
1                      2019-03-15        15
1                      2019-03-15        50
9                      2019-03-15        21
9                      2019-03-15        18

我需要结果:

USER_ID                CDATE             REVENUE
1                      2019-03-15        80
2                      2019-03-15        18
5                      2019-03-15        29
9                      2019-03-15        50

我想提一下，每个用户在这两个表中都没有行，因此某些用户没有收入/表中没有行(例如，用户 5 仅在表 user_revenue 中具有行，而不在表 user_revenue_publisher 中)。

Answer 1

一种方法是使用 UNION ALL 集合运算符将两个结果集合并为一个集合，然后将该查询用作内联 View 。外部查询可以执行使用 GROUP BY 操作来折叠行，并使用 SUM 聚合函数来累加收入。

类似这样的事情:

SELECT q.user_id
     , q.cdate
     , SUM(q.revenue) AS revenue
  FROM ( SELECT r.user_id
              , r.cdate
              , r.revenue
           FROM user_revenue r
          WHERE r.user_id  = ?
            AND r.cdate   >= DATE(NOW()) + INTERVAL -30 DAY
          UNION ALL
         SELECT p.user_id
              , p.cdate
              , p.revenue
           FROM user_revenue_publisher p
          WHERE p.user_id  = ?
            AND p.cdate   >= DATE(NOW()) + INTERVAL -30 DAY
       ) q
 GROUP
    BY q.user_id
     , q.cdate
 ORDER
    BY q.user_id
     , q.cdate

<小时/>

还有其他查询模式将返回可能(或更好)以更好的性能满足规范的结果。