t_score是一个记录学生成绩的数据表，表的创建脚本如下：

-- 创建成绩表t_score-- sname表示学生姓名，cname表示课程名称，grade表示考试成绩CREATE TABLE t_score(sname VARCHAR(10), cname VARCHAR(10), score INTEGER);
-- 插入测试数据INSERT INTO t_score(sname, cname, grade) VALUES ('张三', '语文', 80);INSERT INTO t_score(sname, cname, grade) VALUES ('李四', '语文', 77);INSERT INTO t_score(sname, cname, grade) VALUES ('王五', '语文', 91);INSERT INTO t_score(sname, cname, grade) VALUES ('张三', '数学', 85);INSERT INTO t_score(sname, cname, grade) VALUES ('李四', '数学', 90);INSERT INTO t_score(sname, cname, grade) VALUES ('王五', '数学', 60);INSERT INTO t_score(sname, cname, grade) VALUES ('张三', '英语', 81);INSERT INTO t_score(sname, cname, grade) VALUES ('李四', '英语', 69);INSERT INTO t_score(sname, cname, grade) VALUES ('王五', '英语', 82);

其中字段sname表示学生姓名，cname表示课程名称，grade表示考试成绩。每行数据代表了一个学生一门学科的成绩。假如现在我们需要以每个学生一行数据的形式创建以下报表：

学生姓名	语文	数学	英语
张三	80	85	81
李四	77	90	69
王五	91	60	82

那么应该如何编写查询语句？

这是一个典型行转换为列的问题，可以利用CASE表达式加上聚合函数实现。我们首先使用CASE表达式将每门学科转换为一列：

SELECT sname AS "学生姓名",       CASE cname WHEN '语文' THEN grade ELSE 0 END AS "语文",       CASE cname WHEN '数学' THEN grade ELSE 0 END AS "数学",       CASE cname WHEN '英语' THEN grade ELSE 0 END AS "英语"FROM t_score;

第1个CASE表达式用于转换学生的语文成绩，cname等于“语文”就返回对应的成绩，否则就返回0。同理，第2个和第3个CASE表达式分别用于转换数学和英语成绩。查询返回的结果如下：

接下来我们通过分组汇总将每个学生的成绩合并成一条记录：

SELECT sname AS "学生姓名",       SUM(CASE cname WHEN'语文' THEN grade ELSE 0 END) AS"语文",       SUM(CASE cname WHEN'数学' THEN grade ELSE 0 END) AS"数学",       SUM(CASE cname WHEN'英语' THEN grade ELSE 0 END) AS"英语"FROM t_scoreGROUP BY sname;

GROUP BY子句将数据按照学生进行分组，SUM()函数对每门学科的成绩进行求和，学科成绩加上2个0还是学科成绩。这样我们就实现了上面的报表。

以上案例来自图书《SQL编程思想》。

ITPub博客

SQL 案例分析：数据报表行列转换

最新文章