mysql 行转列 成绩汇总,查询每个学生每门课程的成绩,sql server行转列实现
时间: 2023-11-22 19:15:49 浏览: 33
在 MySQL 中,可以使用 GROUP_CONCAT 和 CASE WHEN 语句实现行转列操作,查询每个学生每门课程的成绩汇总,示例 SQL 语句如下:
```
SELECT
s.name AS student_name,
GROUP_CONCAT(
CONCAT(c.name, ':',
CASE WHEN sc.score IS NULL THEN '无成绩' ELSE CAST(sc.score AS CHAR) END)
ORDER BY c.id
SEPARATOR ', '
) AS scores
FROM
student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.id
GROUP BY
s.id;
```
而在 SQL Server 中,可以使用 PIVOT 和 UNPIVOT 关键字实现行转列操作,示例 SQL 语句如下:
```
SELECT
student_name,
course_name + ': ' + CAST(score AS VARCHAR(10)) AS score
FROM
(SELECT
s.name AS student_name,
c.name AS course_name,
CAST(sc.score AS FLOAT) AS score
FROM
student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.id) AS source_data
PIVOT
(MAX(score) FOR course_name IN ([语文], [数学], [英语])) AS pivot_data;
```
其中,MAX(score) 可以替换为其他聚合函数,如SUM、AVG等。同时,UNPIVOT 关键字则可以实现列转行操作。