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 |
+------------+-------------+---------+----------+----------+

Answer 2

这假设ID列与服务状态和支付状态一起相关...

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

注意，我知道你想如何处理7(无状态记录)和12 (无收费记录)。这目前只计算了存在的内容。

或者，如果您不想按ID关联记录，您仍然可以按日期关联，但您需要更改您的逻辑。

目前你得到了这个，因为你只通过日期来联系...

id | charged |    date           id  | status |   date
-----------------------------    --------------------------
8  |  OK     |  2011-12-03        8  |   1    | 2011-12-03
8  |  OK     |  2011-12-03        9  |   1    | 2011-12-03

7  |  OK     |  2011-12-03        8  |   1    | 2011-12-03
7  |  OK     |  2011-12-03        9  |   1    | 2011-12-03

9  |  NO     |  2011-12-03        8  |   1    | 2011-12-03
9  |  NO     |  2011-12-03        9  |   1    | 2011-12-03

11 |  OK     |  2011-12-04        11 |   0    | 2011-12-04
11 |  OK     |  2011-12-04        12 |   0    | 2011-12-04
11 |  OK     |  2011-12-04        14 |   1    | 2011-12-04

14 |  NO     |  2011-12-04        11 |   0    | 2011-12-04
14 |  NO     |  2011-12-04        12 |   0    | 2011-12-04
14 |  NO     |  2011-12-04        14 |   1    | 2011-12-04

相反，您需要将数据合并到每个表的每个日期为1，然后连接...

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

  (
   SELECT
     date,
     SUM(CASE WHEN charged = '0' THEN 1 ELSE 0 END) AS status_0,
     SUM(CASE WHEN charged = '1' THEN 0 ELSE 1 END) AS status_1
   FROM
     status
   GROUP BY
     date
  )
  AS status
    ON charge.date = status.date

还有其他方法，但希望这能为您解释一点。

Answer 3

我建议使用UNION ALL：

select date, 
       coalesce(sum(not_charged),0) not_charged, 
       coalesce(sum(charged),0) charged, 
       coalesce(sum(status_1),0) status_1, 
       coalesce(sum(status_0),0) status_0
from (select date,
             case charged when 'NO' then 1 end not_charged,
             case charged when 'OK' then 1 end charged,
             0 status_1,
             0 status_0
      from charge
      union all
      select date,
             0 not_charged,
             0 charged,
             case status when '1' then 1 end status_1,
             case status when '0' then 1 end status_0
      from status) sq
group by date