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 个表的所有字段
1 2 3 4 5 6 7 8 9 10 11 12
mysql>SELECT a.*, b.*FROM ( SELECT t.*FROM table1 t WHERE `status` ='finished' ) AS a LEFTJOIN ( 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| +-----------------+----------+-------+-----------------+------------+-------+
mysql>SELECT a.department_code, a.total as finished, b.total as unfinished FROM ( SELECT t.*FROM table1 t WHERE `status` ='finished' ) AS a LEFTJOIN ( 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 值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
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 LEFTJOIN ( 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 表作为左表的处理。– 或直接转为右连接。。
右连接合表
1 2 3 4 5 6 7 8 9 10 11 12 13 14
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 RIGHTJOIN ( 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 的数据。先别慌!!!。
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 LEFTJOIN ( 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 RIGHTJOIN ( 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 LEFTJOIN ( 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 RIGHTJOIN ( 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| +-----------------+----------+------------+