MySQL(二十四)：记一条要实行类似于行转列的统计SQL实现过程

需求

原始数据

多表关联产生的初步的统计数据如下：

+-----------------+------------+-------+
| department_code | status     | total |
+-----------------+------------+-------+
| 111             | finished   |     1 |
| 111             | unfinished |     2 |
| 222             | finished   |     1 |
+-----------------+------------+-------+

需求数据

现在转换生成目标统计数据格式如下：

+-----------------+----------------+------------------+
| department_code | finished       | unfinished       |
+-----------------+----------------+------------------+
| 111             |              1 |                2 |
| 222             |              1 |                0 |
+-----------------+----------------+------------------+

实现

拆表

第一步需要按 status 拆分为 2 个表，如下：

mysql> SELECT t.* FROM table1 t WHERE `status` = 'finished';
+-----------------+----------+-------+
| department_code | status   | total |
+-----------------+----------+-------+
| 111             | finished |     1 |
| 222             | finished |     1 |
+-----------------+----------+-------+

mysql> SELECT t.* FROM table1 t WHERE `status` = 'unfinished';
+-----------------+------------+-------+
| department_code | status     | total |
+-----------------+------------+-------+
| 111             | unfinished |     2 |
+-----------------+------------+-------+

左连接合表

把两个表合使用连接合并，就展示 2 个表的所有字段

mysql> SELECT a.*, b.* FROM
    ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
    LEFT JOIN 
    ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
    ON a.department_code = b.department_code;
+-----------------+----------+-------+-----------------+------------+-------+
| department_code | status   | total | department_code | status     | total |
+-----------------+----------+-------+-----------------+------------+-------+
| 111             | finished |     1 | 111             | unfinished |     2 |
| 222             | finished |     1 | NULL            | NULL       | NULL  |
+-----------------+----------+-------+-----------------+------------+-------+

上面可以看到左表是 finished 的数据，右表是 unfinished 的数据；接下来就需要抽取数据了

抽取数据

抽取数据主要把合表相同的字段名按拆表重命名

mysql> SELECT a.department_code, a.total as finished, b.total as unfinished FROM
    ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
    LEFT JOIN 
    ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
    ON a.department_code = b.department_code;
+-----------------+----------+------------+
| department_code | finished | unfinished |
+-----------------+----------+------------+
| 111             |        1 |          2 |
| 222             |        1 | NULL       |
+-----------------+----------+------------+

处理NULL值

使用 IFNULL 过程函数处理 NULL 值

mysql> SELECT 
    a.department_code, 
    IFNULL(a.total, 0) as finished,
    IFNULL(b.total, 0) as unfinished
    FROM
    ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
    LEFT JOIN 
    ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
    ON a.department_code = b.department_code;
+-----------------+----------+------------+
| department_code | finished | unfinished |
+-----------------+----------+------------+
| 111             |        1 |          2 |
| 222             |        1 |          0 |
+-----------------+----------+------------+

到这里完了吗，数据表面上看起来已经满足需求了，但不是真正的完成，存在BUG！！！！！！！

注意：

• 上面的合表使用的是左连接，当 a 表的数据行大于 b 表的数据行时是可以满足的；

• 当 a 表的数据行小于 b 表时，仍使用 a 表作为左连接并加上条件匹配，就存在丢失 b 表数据的问题。

  要解决此问题，只需把数据行多的表作为左连接的左表，但实际不可能先查出数据谁的行数多，然后动态匹配作为左表。

  这时就需要再做一次左连接处理，把 b 表作为左表，前面已经有一次 a 表作为左表的处理。-- 或直接转为右连接。

右连接合表

mysql> SELECT
    a.department_code,
    IFNULL(a.total, 0) as finished,
    IFNULL(b.total, 0) as unfinished
FROM
    ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
    RIGHT JOIN 
    ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
    ON a.department_code = b.department_code;
+-----------------+----------+------------+
| department_code | finished | unfinished |
+-----------------+----------+------------+
| 111             |        1 |          2 |
+-----------------+----------+------------+

上面示例使用了右连接表，只有一条数据的 a 表作为右表，导致有两条数据 b表丢失了一条 department_code = 222 的数据。先别慌！！！。

进行到这里可以知道，要想确保不丢失数据，必须同时使用左连接合表，右连接合表，最后再把这两条表取并集并去重。

取合表并集

使用 UNION 去重取上面两个合表的并集，这才是最终真正要的数据。

mysql> SELECT
        a.department_code,
        IFNULL( a.total, 0 ) AS finished,
        IFNULL( b.total, 0 ) AS unfinished 
    FROM
        ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
        LEFT JOIN 
        ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
        ON a.department_code = b.department_code UNION
    SELECT
        a.department_code,
        IFNULL( a.total, 0 ) AS finished,
        IFNULL( b.total, 0 ) AS unfinished 
    FROM
        ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
        RIGHT JOIN 
        ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
        ON a.department_code = b.department_code ;
+-----------------+----------+------------+
| department_code | finished | unfinished |
+-----------------+----------+------------+
| 111             |        1 |          2 |
| 222             |        1 |          0 |
+-----------------+----------+------------+

获取结果数据

将已合并集的表作为子表并从中获取最终的结果数据

mysql> SELECT
    t.department_code,
    IFNULL( t.finished, 0 ) AS finished,
    IFNULL( t.unfinished, 0 ) AS unfinished 
FROM
    (
    SELECT
        a.department_code,
        IFNULL( a.total, 0 ) AS finished,
        IFNULL( b.total, 0 ) AS unfinished 
    FROM
        ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
        LEFT JOIN 
        ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
        ON a.department_code = b.department_code UNION
    SELECT
        a.department_code,
        IFNULL( a.total, 0 ) AS finished,
        IFNULL( b.total, 0 ) AS unfinished 
    FROM
        ( SELECT t.* FROM table1 t WHERE `status` = 'finished' ) AS a
        RIGHT JOIN 
        ( SELECT t.* FROM table1 t WHERE `status` = 'unfinished' ) AS b 
        ON a.department_code = b.department_code 
    ) AS t;
+-----------------+----------+------------+
| department_code | finished | unfinished |
+-----------------+----------+------------+
| 111             |        1 |          2 |
| 222             |        1 |          0 |
+-----------------+----------+------------+