mysql - Mysql计数或转置时的值计数

This question already has answers here:

How can I return pivot table output in MySQL?
                                
                                    （8个答案）
                                
                        
                                2年前关闭。
            
                    
我的数据库中有一个表

+ ------ + ------ + ---------- +
| id |部门|位置|
+ ------ + ------ + ---------- +
| 1 | eee |钦奈|
| 2 | ece |钦奈|
| 3 | eee |钦奈|
| 4 | ece |钦奈|
| 5 | eee | cbe |
| 6 | ece |特里奇
| 7 |机械madurai |
+ ------ + ------ + ---------- +


我需要在转置该列之一时使用id计数。谁能建议我查询以得到以下结果

+ --------- + ------ + ----- + ------ +
|位置| eee | ece |机械
+ --------- + ------ + ----- + ------ +
|钦奈| 2 | 2 | 0 |
| cbe | 1 | 0 | 0 |
|特里奇0 | 1 | 0 |
| madurai | 0 | 0 | 1 |
+ --------- + ------ + ----- + ------ +


感谢您的任何帮助

最佳答案

select location as city,
       sum(dept = 'eee') as eee,
       sum(dept = 'ece') as ece,
       sum(dept = 'mech') as mech
from your_table
group by location