一直在寻找将行(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