mysql - 如何在 MySQL 中使用 group by 和 pivot？

Question

任何人都可以帮我将 2 个表分组并显示如下...

我的第一个表是:

user_id | department-----------------------1       | user1----------------------2       | user2----------------------

表 2 是:

reserve_id | user_id | reserve_status--------------------------------------    1      |     1   |        1--------------------------------------    2      |     1   |        1--------------------------------------    3      |     1   |        2--------------------------------------    4      |     2   |        2--------------------------------------    5      |     2   |        0--------------------------------------    6      |     2   |        1*reserve_status 0 uncheck, 1 = approve , 2 = disapproval

我想要这样的产品:

department | sumreserve | sumreservapprove | sumreserveunapprove | uncheck---------------------------------------------------------------------------- user1     |     3      |        2         |         1           |    0---------------------------------------------------------------------------- user2     |     3      |        1         |         1           |    1

抱歉英语不好

Answer 1

您需要一个枢轴解决方案。

演示:
假设 so_q27895250_users 和 so_q27895250_reserve 作为表名

mysql>
mysql> select u.department, count(r.reserve_status) as total_reserves,
    ->     count( case when r.reserve_status = 0
    ->                         then r.reserve_status else null end ) as unchecked,
    ->     count( case when r.reserve_status = 1
    ->                         then r.reserve_status else null end ) as approved,
    ->     count( case when r.reserve_status = 2
    ->                         then r.reserve_status else null end ) as unapproved
    ->   from so_q27895250_reserve r
    ->     left join so_q27895250_users u
    ->                    on r.user_id = u.user_id
    ->  group by r.user_id
    -> ;
+------------+----------------+-----------+----------+------------+
| department | total_reserves | unchecked | approved | unapproved |
+------------+----------------+-----------+----------+------------+
| user1      |              3 |         0 |        2 |          1 |
| user2      |              3 |         1 |        1 |          1 |
+------------+----------------+-----------+----------+------------+
2 rows in set (0.00 sec)