我有下表，我应该计算每个列名的条目(5，4，3，2，1)
| ID | ClientID | Responsiveness1 | Responsiveness2 | Reliability1 |
|---|---|---|---|---|
| 1024 | 3511 | 5 | 4 | 3 |
| 1025 | 4571 | 5 | 3 | 5 |
| 1026 | 3827 | 4 | 5 | 4 |
| 1027 | 7652 | 1 | 1 | 1 |
| 1028 | 7778 | 2 | 2 | 2 |
| 1029 | 7612 | 1 | 1 | 2 |
我try 按列名和范围(5，4，3，2，1)手动计数(从Responsiveness1=‘5’的表中 Select Count(Responsiveness1))，并得到输出.但不幸的是，我必须按列和按范围计算它，然后将其显示在我的表中.
有没有一种更简单、更有效的方法来实现以下输出:
| Rate | Responsiveness1 | Responsiveness2 | Reliability1 |
|---|---|---|---|
| 5 | 2 | 1 | 1 |
| 4 | 1 | 1 | 1 |
| 3 | 0 | 1 | 1 |
| 2 | 1 | 1 | 2 |
| 1 | 2 | 2 | 1 |
而不必按列名计数，然后将其插入到输出表中？
是的，您可以使用MySQL SUM函数来统计每一列的每个评级出现的次数，然后使用CASE语句将每个评级映射到相应的列.
以下是一个示例查询:
SELECT
rating.Rate,
SUM(CASE WHEN t.Responsiveness1 = rating.Rate THEN 1 ELSE 0 END) AS Responsiveness1,
SUM(CASE WHEN t.Responsiveness2 = rating.Rate THEN 1 ELSE 0 END) AS Responsiveness2,
SUM(CASE WHEN t.Reliability1 = rating.Rate THEN 1 ELSE 0 END) AS Reliability1
FROM
my_table t
CROSS JOIN (SELECT 5 AS Rate UNION ALL SELECT 4 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 1) rating
GROUP BY
rating.Rate
ORDER BY
rating.Rate DESC;
SQL小提琴Output