Mysql内连接查询

Question

我在数据库中使用了两个表。第一个包含与成功和不成功付款相关的数据，而第二个表包含有关服务状态的数据。

查询的结果应该结合两个表，结果列出按天分组的成功和不成功的付款以及按天分组的服务状态。

第一个表看起来像:

id | charged |    date
-----------------------------
8  |  OK     |  2011-12-03
7  |  OK     |  2011-12-03
9  |  NO     |  2011-12-03
11 |  OK     |  2011-12-04
14 |  NO     |  2011-12-04

第二个表看起来像:

id  | status |   date
--------------------------
 8  |   1    | 2011-12-03
 9  |   1    | 2011-12-03
 11 |   0    | 2011-12-04
 12 |   0    | 2011-12-04
 14 |   1    | 2011-12-04

正确的查询结果应该是:

   date    | not_charged | charged | status_1 | status_0  
-----------------------------------------------------------
2011-12-04 |      1      |   1     |    1     |    2
2011-12-03 |      1      |   2     |    2     |    0

我试过的查询如下所示:

SELECT i.date, SUM(
CASE WHEN i.charged = 'NO'
THEN 1 ELSE 0 END ) AS not_charged, SUM(
CASE WHEN i.charged = 'OK'
THEN 1 ELSE 0 END ) AS charged, SUM(
CASE WHEN s.status = '1'
THEN 1 ELSE 0 END ) AS status_1, SUM(
CASE WHEN s.status = '0' THEN 1 ELSE 0 END ) AS status_0
FROM charge i INNER JOIN status s ON s.date = i.date
GROUP BY i.date

但是我得到的结果是这样的错误

   date    | not_charged | charged | status_1 | status_0
---------------------------------------------------------
2011-12-04 |     3       |    3    |    2     |    4
2011-12-03 |     2       |    4    |    6     |    0

我哪里做错了，我怎样才能得到正确的结果？

感谢所有建议。

Answer 1

试试这个 -

SELECT date,
  SUM(IF(charged = 'NO', 1, 0)) not_charged,
  SUM(IF(charged = 'OK', 1, 0)) charged,
  SUM(IF(status = 1, 1, 0)) status_1,
  SUM(IF(status = 0, 1, 0)) status_0
FROM (
  SELECT date, charged, NULL status FROM charge
    UNION ALL
  SELECT date, NULL charged, status FROM status
    ) t
  GROUP BY date DESC;

+------------+-------------+---------+----------+----------+
| date       | not_charged | charged | status_1 | status_0 |
+------------+-------------+---------+----------+----------+
| 2011-12-04 |           1 |       1 |        1 |        2 |
| 2011-12-03 |           1 |       2 |        2 |        0 |
+------------+-------------+---------+----------+----------+