WHAT?
行列倒置,就是当你想要的数据正好和数据库中用SQL语句查询出来的表格数据行列正好相反的时候,比如如下查询:
SELECT t_grade.date as 日期, SUM(t_detailrecord.getGrade) AS 分数,t_categray.caName as 类别名称,t_grade.recordId as ID,t_grade.dayGrade as 日结,t_grade.totalGrade as 总分,t_grade.userId as UID
FROM t_grade JOIN t_detailrecord on t_grade.recordId=t_detailrecord.recordId
join t_setgrade ON t_detailrecord.questId=t_setgrade.questId
join t_categray ON t_categray.caId=t_setgrade.parentId
GROUP BY t_categray.caId,t_grade.recordId
查出的结果:
但是用户的需求如下:
“我要这表格有何用??我要这分类又如何??我想要的是以日期为唯一标识符,为主键,把类别名称下的A B C 分别3列显示,而不要这样一列让我看,日结的分,就是前面3列类别ABC的分数的总和!!”
这!!数据库是设计是一定的,和其他需求有联系,不能随便改了,我们聪明的开发团队就各种找资料,还真找到了一种让行和列倒置的方法。
效果如图:
这样,我们就按照用户的需求,把表中的行和列倒置了。
HOW?如何实现呢?
SELECT date,
MAX(CASE WHEN e.caName = '卫生分' THEN e.grade ELSE NULL END) AS 小明的A类分,
MAX(CASE WHEN e.caName = '番茄分' THEN e.grade ELSE NULL END) AS B类分,
MAX(CASE WHEN e.caName = '纪律分' THEN e.grade ELSE NULL END) AS C类分,
MAX(CASE WHEN e.caName = '每日发现' THEN e.grade ELSE NULL END) AS D类分,
MAX(CASE WHEN e.dayGrade!='' THEN e.dayGrade ELSE NULL END) AS 日结,
MAX(CASE WHEN e.totalGrade!='' THEN e.totalGrade ELSE NULL END ) AS 总分
FROM
(SELECT t_grade.date as date, SUM(t_detailrecord.getGrade) AS grade,t_categray.caName,t_grade.recordId,t_grade.dayGrade,t_grade.totalGrade,t_grade.userId
FROM t_grade JOIN t_detailrecord on t_grade.recordId=t_detailrecord.recordId
join t_setgrade ON t_detailrecord.questId=t_setgrade.questId
join t_categray ON t_categray.caId=t_setgrade.parentId
GROUP BY t_categray.caId,t_grade.recordId
) AS e
GROUP BY date
就是这样,so easy~~聪明的你也可以对比自己的需求来重新写这段代码。
小结:
不怕不知道,就怕不知道,勇敢的去试吧,多和你的团队交流!