mysql - SQL中如何对3个以上的表求和

Question

下面我有4张 table

table : user

id_tkn  |   nm_user     |
-----------------------------------------
    1   |   belva       |
    2   |   nanda       |
    3   |   maya        |
-----------------------------------------

table : maintenance

id_mntnc|id_tkn |   sts |   date    |
-----------------------------------------------------------------
    1   |   2   |   1   |2016-03-03 |
    2   |   2   |   2   |2016-03-03 |
    3   |   1   |   1   |2016-03-03 |
    4   |   2   |   0   |2016-03-03 |
    5   |   2   |   1   |2016-03-03 |
-----------------------------------------------------------------

table : Installasi

id_istlsi|id_tkn|   sts |   date    |
-----------------------------------------------------------------
    1   |   2   |   1   |2016-03-03 |
    2   |   1   |   1   |2016-03-03 |
    3   |   1   |   1   |2016-03-03 |
    4   |   2   |   1   |2016-03-03 |
    5   |   3   |   1   |2016-03-03 |
-----------------------------------------------------------------

table : visit

id_vst  |id_tkn |   sts |   date    |
-----------------------------------------------------------------
    1   |   2   |   1   |2016-03-03 |
    2   |   2   |   0   |2016-03-03 |
    3   |   1   |   1   |2016-03-03 |
-----------------------------------------------------------------

关于“sts”列的信息 0 --> 待定 1 --> 成功 2 --> 失败。从上表中，我想通过 status(sts) where id_tkn = 2 相加，结果如下表，如何生成 SQL 命令来生成下表？

id_tkn  |   nm_usr  | maintenance_suc | maintenance_fail | installasi_suc| installasi_fail | visit_suc| visit_fail
-----------------------------------------------------------------------------------------------------------------------
   2    |   nanda   |      2           |       1         |     2         |     0           |     1    |  0

Answer 1

这应该可以正常工作，但可能需要优化:

select 
    u.id_tkn, 
    u.nm_user,
    sum ( case when stat.sts = 1 and stat.typ = 'm' then 1 else 0 end ) as maintenance_suc,
    sum ( case when stat.sts = 2 and stat.typ = 'm' then 1 else 0 end ) as maintenance_fail,
    sum ( case when stat.sts = 1 and stat.typ = 'i' then 1 else 0 end ) as installasi_suc,
    sum ( case when stat.sts = 2 and stat.typ = 'i' then 1 else 0 end ) as installasi_fail,
    sum ( case when stat.sts = 1 and stat.typ = 'v' then 1 else 0 end ) as visit_suc,
    sum ( case when stat.sts = 2 and stat.typ = 'v' then 1 else 0 end ) as visit_fail
from
user u
left join 
(
    select sts, 'm', id_tkn
    from 
    maintenance
    union all
    select sts, 'i', id_tkn
    from 
    Installasi
    union all
    select sts, 'v', id_tkn
    from 
    visit
) stat on u.id_tkn = stat.id_tkn
where u.id_tkn = 2
group by u.id_tkn, u.nm_user