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

我做错了什么，如何得到正确的结果？

感谢所有建议。

- Sergio

Answer 1

这假设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

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

- MatBailie

Answer 2

试试这个 -

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

- Devart

---

+1：如果记录不需要按其ID进行关联，则最简洁的选项。 - MatBailie

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

- user359040

---

在 UNION 之前和之后进行预聚合，是否可以节省任何资源成本？ - MatBailie

据我所知，没有这样的情况——如果有的话，我会预计会有（非常）轻微的性能损失，因为相同的数据将被聚合两次，尽管我怀疑实际差异太小而无法辨别。 - user359040