mysql 单列转换为行_如何将行转换为列MySQL

一直在寻找将行(progress_check)转移到列检查1,检查2等…

不需要总和或总计,只是为了显示结果…

任何人都可以帮助,谢谢广告

解决方法:

这是使用标准SQL(以及MySQL实现的标准部分)进行透视的一种方法.这意味着它不仅适用于MySQL,而且适用于大多数SQL数据库：

SELECT

r0.sims_id,

r0.subject,

r1.result AS "C1",

r2.result AS "C2",

r3.result AS "C3"

FROM

(SELECT DISTINCT

sims_id, subject

FROM

results

) r0

LEFT JOIN results r1

ON r1.sims_id = r0.sims_id AND r1.subject = r0.subject AND r1.progress_check = 'C1'

LEFT JOIN results r2

ON r2.sims_id = r0.sims_id AND r2.subject = r0.subject AND r2.progress_check = 'C2'

LEFT JOIN results r3

ON r3.sims_id = r0.sims_id AND r3.subject = r0.subject AND r3.progress_check = 'C3'

ORDER BY

r0.sims_id, r0.subject ;

虽然对于这种问题(sims_id,subject-id,progress_check)应该是PRIMARY KEY(或者至少是UNIQUE),但是使用这种方法,它有“C1”,“C2”或“C3”的重复值.对于单个sims_id,主题…所有可用信息的笛卡尔积显示在结果中.如果丢失也没有信息,也没有总结.这种行为是否合适取决于用例.

标签：mysql,pivot

来源： https://codeday.me/bug/20190806/1601963.html