【MySQL】行转列 与 列转行

行转列

比如说，我们对学生分数字段进行了GROUP BY操作后，需要看这个分数对应的学生分别是谁，就可以使用GROUP_CONCAT函数来行转列

SELECT score, GROUP_CONCAT(student_name) AS '学生名字'
FROM t_student_score
GROUP BY score

score	学生名字
90	张三,李四,王五
80	周大生,王二狗
60	李狗蛋

列转行

我们使用GROUP_CONCAT函数转换后的字段，如果还想利用这个字段进行IN查询，那么就需要按,进行拆分。
比如，我们查询出分数一样的同学后，还想要知道分数一样这些同学的平均智商。

错误示例

SELECT score,
(
	-- 计算相同分数学生的平均IQ
	SELECT SUM(iq) / COUNT(*) FROM t_student_iq 
	WHERE student_id IN (t.studentIds)
) AS avgIQ
FROM (
	SELECT score, GROUP_CONCAT(student_id) AS studentIds
	FROM t_student_score
	GROUP BY score
) AS t

错误原因分析：

这里由GROUP_CONCAT函数返回的studentIds是字符串，如："1,3,5,7"

如果直接用这个字段进行IN查询就变成了：

SELECT SUM(iq) / COUNT(*) FROM t_student_iq 
WHERE student_id IN ("1,3,5,7")

所以我们需要对studentIds进行拆分处理，请看正确示例。

正确示例

SELECT score,
(
	-- 计算相同分数学生的平均IQ
	SELECT SUM(iq) / COUNT(*) FROM t_student_iq 
	WHERE student_id IN (
		SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.studentIds, ',', help_topic_id+1), ',', -1) AS Id FROM mysql.help_topic WHERE help_topic_id  < (LENGTH(t.studentIds) - LENGTH(REPLACE(t.studentIds, ',' , '')) + 1)	
	)
) AS avgIQ
FROM (
	SELECT score, GROUP_CONCAT(student_id) AS studentIds
	FROM t_student_score
	GROUP BY score
) AS t

score	avgIQ
90	100
80	96
60	86

SQL分析：
这里使用了一句SQL将GROUP_CONCAT函数返回的studentIds进行了拆分转换，

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.studentIds, ',', help_topic_id+1), ',', -1) AS Id 
FROM mysql.help_topic 
WHERE help_topic_id  < (LENGTH(t.studentIds) - LENGTH(REPLACE(t.studentIds, ',' , '')) + 1)	

最终实现了真正的IN查询，即：

SELECT SUM(iq) / COUNT(*) FROM t_student_iq 
WHERE student_id IN (1,3,5,7)

涉及知识点：

• substring_index 拆分函数
  • substring_index(str, delim, count)
    • str 要处理的字符串
    • delim 分隔符
    • count 计数
• mysql.help_topic 默认自增序列表