MySQL行转列

原始数据

初始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