从列到行让 mysql 两表数据变得更清晰（mysql两表列转行）
随着数据量的增加和业务需求的复杂化，数据报表和统计分析对数据的准确性和完整性要求越来越高。而在 MySQL 数据库中，我们经常需要通过联表查询来完成多表数据的关联和分析。但是，如果需要分析的数据涉及到多个表，这种方式很容易使查询语句变得复杂和冗长，同时也会降低查询效率。
为了让多表关联查询更容易和高效，我们可以使用 MySQL 的 Pivot 操作，将多个数据表中的列转换成行，以便更好地理解和操作数据。
1. 什么是 Pivot 操作？
Pivot 操作是一种将关系数据库中列转换为行的技术，旨在方便用户对多表数据进行分析。通过 Pivot 操作，我们可以将一个表格中的列（称为源列）转换为若干新列，而新列则成为行。这种技术也称为“交叉表查询”、“行列转换”、“转置”等。
2. Pivot 操作的实现方式
MySQL 支持 Pivot 操作，实现方式有两种：
1） 使用 CASE WHEN 语句
CASE WHEN 语句是 MySQL 中一个非常实用的分支语句，在 Pivot 操作中也可以使用。使用 CASE WHEN 语句实现 Pivot 操作的基本思路是，根据某些条件判断源列的值，并将其转换为新列的值。
例如，我们有两个表格 t_student 和 t_score，其中 t_student 包含学生的基本信息（包括学号、姓名、性别等），而 t_score 包含学生的科目成绩信息（包括学号、科目、成绩等）。现在我们需要查询每个学生的数学成绩、英语成绩、物理成绩等信息。使用 CASE WHEN 语句，我们可以像下面这样实现：
SELECT student_id, MAX(CASE WHEN subject=’数学’ THEN score ELSE NULL END) AS ‘数学’, MAX(CASE WHEN subject=’英语’ THEN score ELSE NULL END) AS ‘英语’, MAX(CASE WHEN subject=’物理’ THEN score ELSE NULL END) AS ‘物理’
FROM t_student
LEFT JOIN t_score
ON t_student.id = t_score.student_id
GROUP BY student_id;
在上面的查询语句中，我们使用了 CASE WHEN 语句来筛选出科目为“数学”、“英语”、“物理”等的成绩记录，通过 MAX 求出每个学生的相应成绩，并按学生编号分组，最终得到一个以学生编号为行，以各门成绩为列的交叉表。
2） 使用动态 SQL
使用动态 SQL 实现 Pivot 操作的基本思路是，构造一个动态的 SQL 语句，根据实际需求生成新列的列名和值。通过这种方式，我们可以灵活地对数据进行排序、过滤、分组等操作，进而生成交叉表。
如下是使用动态 SQL 实现 Pivot 操作的示例代码：
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT(‘MAX(CASE WHEN subject = “‘, subject,
‘” THEN score ELSE NULL END) AS “‘, subject, ‘”‘)
) INTO @sql
FROM t_score;
SET @sql = CONCAT(‘SELECT student_id, ‘, @sql, ‘ FROM t_student LEFT JOIN t_score ON t_student.id = t_score.student_id GROUP BY student_id’);
在上述代码中，我们首先定义了一个变量 @sql，用来存储动态 SQL 语句。然后使用 GROUP_CONCAT 函数将要生成的新列的列名和值连接起来，通过 CONCAT 函数构造出完整的 SQL 语句，并将其赋值给变量 @sql。最后执行动态 SQL 语句，即可得到我们需要的交叉表。
3. Pivot 操作的注意事项
使用 Pivot 操作时，我们需要注意以下几点：
1） Pivot 操作会增加查询语句的复杂度和运行时间。因此应该在必要的情况下使用 Pivot 操作，避免查询语句过于复杂和冗长。
2）Pivot 操作的实现方式对性能和可读性有一定的影响。使用 CASE WHEN 语句的 Pivot 操作适用于列名比较少，数据量比较小的情况，而使用动态 SQL 的 Pivot 操作适用于列名数量较多，数据量较大的情况。
3）Pivot 操作需要创建新的列，这可能会带来额外的数据存储和维护成本。当我们不需要交叉表的时候，应该避免使用 Pivot 操作，以减少数据库空间的占用。
通过 Pivot 操作将多个数据表中的列转换成行，可以使多表关联查询更加便捷和高效，提升数据分析的准确性和完整性，同时也需要我们在使用 Pivot 操作时注意其适用范围和注意事项。
