mysql - 我如何从mysql中的两个表中计算

Question

下面有2张 table

0 --> Pending
1 --> Success
2 --> Fail

table : mntnc
+-------+-------+-------+
|   id  |   own |   sts |
+-------+-------+-------+
|   1   |   BN  |   1   |
|   2   |   BB  |   2   |
|   3   |   BN  |   1   |
|   4   |   BD  |   1   |
|   5   |   BD  |   0   |

table : istlsi
+-------+-------+-------+
|   id  |   own |   sts |
+-------+-------+-------+
|   1   |   BN  |   1   |
|   2   |   BB  |   1   |
|   3   |   BB  |   1   |
|   4   |   BC  |   0   |
|   5   |   BD  |   2   |

上面两个表，我想把它们都加起来成为下面的表

+-------+-----------+-----------+-----------+
|   own |   success |   fail    |   pending |
+-------+-----------+-----------+-----------+
|   BN  |       3   |       0   |       0   |
|   BB  |       2   |       1   |       0   |
|   BD  |       1   |       1   |       1   |
|   BC  |       0   |       0   |       1   |

Answer 1

这里有两个关键点:

1. 联合表(我将结果别名为 B)
2. 对每一列使用 sum(case...)。

首先，我们将两个表联合在一起作为一个内联 View 。

然后，我们对每个所需的列使用 case 语句，并根据 sts 值评估状态，将值设置为 1 或 0。然后总结这些...

SELECT own
     , sum(case when sts=1 then 1 else 0 end) as Success
     , sum(case when sts=2 then 1 else 0 end) as Fail
     , sum(case when sts=0 then 1 else 0 end) as Pending
FROM ( SELECT ID, own, sts
       FROM mntnc
       UNION ALL
       SELECT id, own, sts 
       FROM istlsi
     ) B
GROUP BY own