mysql - 在 MySQL 上设置列名和行值

Question

这是我对数据库 MySQL 的 sql 查询:

SELECT
    sRdt 
    COUNT(*) AS sNumber,
    sYear
FROM
    `doTable`
GROUP BY
    sRdt,
    sYear;

这是输出:

+------+-------+---------+
| sRdt | sYear | sNumber |
+------+-------+---------+
| UOT  | 2014  |      62 |
| UOT  | 2015  |     377 |
| UOT  | 2016  |    4941 |
| UOT  | 2017  |    1426 |
+------+-------+---------+

如何使用其他输出来代替？

+------+-------+-------+-------+-------+
| sRdt | 2014  | 2015  | 2016  | 2017  |
+------+-------+-------+-------+-------+
| UOT  |    62 |  377  | 4941  | 1426  |
+------+-------+-------+-------+-------+

Answer 1

您可以通过一些有条件的金额来做到这一点:

select  sRdt,
        sum(case when sYear = 2014 then 1 end) as col_2014,
        sum(case when sYear = 2015 then 1 end) as col_2015,
        sum(case when sYear = 2016 then 1 end) as col_2016,
        sum(case when sYear = 2017 then 1 end) as col_2017
from    doTable
group by sRdt