我想获取一些行值，作为一个月的列日期

Question

我想要获取一些行值作为哪一列是读取日期基础的列总和

SELECT * FROM `mitre_details`;

id       reading            date
1         500               2019-07-15
2         300               2019-07-15
3         600               2019-07-15
4         800               2019-07-15
5         900               2019-07-15
1         200               2019-07-16
2         250               2019-07-16
3         400               2019-07-16
4         550               2019-07-16
5         790               2019-07-16

已尝试查询：

SELECT date,sum(reading) FROM `mitre_details` GROUP BY date;

所需输出：

Date          Reading    Mitre 1    Mitre 2   Mitre 3   Mitre 4   Mitre 5
2019-07-15     3100       500         300       600       800       900
2019-07-16     2190       200         250       400       550       790

Answer 1

尝尝这个,

select `date`,
sum(reading) Reading,
group_concat(case when id=1 then reading end) m1,
group_concat(case when id=2 then reading end) m2,
group_concat(case when id=3 then reading end) m3,
group_concat(case when id=4 then reading end) m4,
group_concat(case when id=5 then reading end) m5
from mitre_details group by `date`;