mysql - 将 2 个或更多结果合并在一起？

Question

请参阅以下 SQL:

SELECT M.username, count(*) as Total, date(status_date) as Date FROM com_result
   LEFT JOIN members as M on M.member_id = com_result.member_id 
GROUP BY date(status_date), com_result.member_id  Order By status_date DESC

这将显示基于 DATE 和 member_id 的记录总数

示例结果:

+----------+-------+------------+
| username | Total | DATE       |
+----------+-------+------------+
| bx7      |     3 | 2012-09-10 |
| bx2      |    25 | 2012-09-04 |
| bx2      |   401 | 2012-09-03 |
| bx1      |   703 | 2012-09-02 |
| bx4      |  1075 | 2012-09-02 |
+----------+-------+------------+

它运行良好，现在我想合并/联合到另一个相同的表结构。 第二条SQL:

SELECT M.username, count(*) as Total, date(status_date) as Date FROM com_result_b
   LEFT JOIN members as M on M.member_id = com_result_b.member_id 
GROUP BY date(status_date), com_result_b.member_id  Order By status_date DESC

假设 com_result 的结果是:

| bx2   |    25 | 2012-09-04 |

com_result_b 的结果将是:

 | bx2   |    50 | 2012-09-04 |

所以当它合并/联合时——我希望结果是这样的:

 | bx2   |    75 | 2012-09-04 |

如您所见，Total 的值从两个表的同一日期开始增加。怎么办？

Answer 1

SELECT
  username,
  IF(q1.Total IS NULL,0,q1.Total)+IF(q2.Total IS NULL,0,q2.Total) AS Total,
  q1.Date
FROM (
  SELECT member_id, M.username, count(*) as Total, date(status_date) as Date 
  FROM com_result
  LEFT JOIN members as M on M.member_id = com_result.member_id 
  GROUP BY date(status_date), com_result.member_id  
) AS q1
LEFT JOIN (
  SELECT member_id, M.username, count(*) as Total, date(status_date) as Date 
  FROM com_result_b
  LEFT JOIN members as M on M.member_id = com_result_b.member_id 
  GROUP BY date(status_date), com_result_b.member_id
) AS q2 ON q1.member_id=q2.member_id AND q1.Date=q2.Date
ORDER BY q1.Date DESC