SQL 查询字段作为列

2022-01-22 00:00:00 pivot sql select mysql

我真的不知道该怎么说，但请检查下面的详细信息.

I dont really know how to put this but please kindly check the details below.

学生

|Student_ID|Student_Name|
|1         |Ryan        |
|2         |Camille     |
|3         |George      |

等级

|Student_ID|Subject |Grade
|1         |Math    |5 
|1         |English |3 
|1         |History |1
|2         |Math    |3 
|2         |English |4 
|2         |History |1 
|3         |Math    |5 
|3         |English |1 
|3         |History |2

有可能得到这个结果吗?

Is it possible to get this result?

Student_Name|Math|English|History
Ryan        |5   |3      |1
Camille     |3   |4      |1
George      |5   |1      |2

现在我一直在通过首先使用列名填充未绑定的数据网格，然后是学生姓名，然后添加每个学生姓名的详细信息来执行此操作.这很耗时，我想更好地优化查询.

Now I've been doing this the hardway by populating an unbound datagrid with first the column name, then the student name then adding the the details for each student name. This is time consuming and I want to optimize the query better.

提前致谢.

推荐答案

如果您有已知数量的主题，@John 的答案将有效，如果您有未知数量的主题，那么您可以使用准备好的语句来动态生成它.这是一篇好文章:

While @John's answer will work if you have a known number of subjects, if you have an unknown number of subjects then you can use prepared statements to generate this dynamically. Here is a good article:

动态数据透视表(将行转换为列)

您的代码如下所示:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when Subject = ''',
      Subject,
      ''' then Grade end) AS ',
      Subject
    )
  ) INTO @sql
FROM grade;

SET @sql = CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM student s
                   LEFT JOIN grade AS g 
                    ON s.student_id = g.student_id
                   GROUP BY s.Student_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle 演示

相关文章