原始数据
初始SQL查询
SELECT
srpt.stu_code,
srdt.change_object,
srdt.change_before,
srdt.change_later
FROM
school_roll_primary_table AS srpt
INNER JOIN school_roll_details_table AS srdt ON srdt.running_id = srpt.running_id
WHERE srpt.stu_code='190212'
期望效果:将行变成列的形式显示
SELECT
srpt.stu_code,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_object ELSE '' END)changeObjectBefore,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_before ELSE '' END) oldMajor,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_before ELSE '' END) oldClass,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_object ELSE '' END) changeObjectLater,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_later ELSE '' END) newMajor,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_later ELSE '' END) newClass
FROM
school_roll_primary_table AS srpt
INNER JOIN school_roll_details_table AS srdt ON srdt.running_id = srpt.running_id
WHERE srpt.stu_code='190212'
GROUP BY srpt.stu_code
`
优化:查出数字对应的名称
SELECT
srpt.stu_code,
ss.name,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_object ELSE '' END)changeObjectBefore,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_before ELSE '' END) oldMajor,
MAX(CASE srdt.change_object WHEN '2' THEN em1.major_name ELSE '' END) oldMajorName,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_before ELSE '' END) oldClass,
MAX(CASE srdt.change_object WHEN '3' THEN ec1.class_name ELSE '' END) oldClassName,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_object ELSE '' END) changeObjectLater,
MAX(CASE srdt.change_object WHEN '2' THEN srdt.change_later ELSE '' END) newMajor,
MAX(CASE srdt.change_object WHEN '2' THEN em2.major_name ELSE '' END) newMajorName,
MAX(CASE srdt.change_object WHEN '3' THEN srdt.change_later ELSE '' END) newClass,
MAX(CASE srdt.change_object WHEN '3' THEN ec2.class_name ELSE '' END) newClassName
FROM
school_roll_primary_table AS srpt
INNER JOIN stu_student ss on ss.stu_code=srpt.stu_code
INNER JOIN school_roll_details_table AS srdt ON srdt.running_id = srpt.running_id
LEFT JOIN edu_major em1 on em1.id=srdt.change_before
LEFT JOIN edu_major em2 on em2.id=srdt.change_later
LEFT JOIN edu_class ec1 on ec1.class_id=srdt.change_before
LEFT JOIN edu_class ec2 on ec2.class_id=srdt.change_later
WHERE srpt.stu_code='190212'
GROUP BY srpt.stu_code