竖行变横行的查询
SELECT stuName,
SUM(CASE project WHEN '语文' THEN stuScore ELSE 0 END) AS 语文, SUM(CASE project WHEN '数学' THEN stuScore ELSE 0 END) AS 数学, SUM(CASE project WHEN '英语' THEN stuScore ELSE 0 END) AS 英语 FROM `t_score` GROUP BY stuName ; ====================================================== -- CREATE [OR REPLACE] [FORCE] VIEW view_name AS subquery [WITH CHECK OPTION ] [WITH READ ONLY] OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图； FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图； subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名； WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束； WITH READ ONLY ：该视图上不能进行任何 DML 操作。 =========================================================== 创建一个如后面查询的结果的视图： CREATE VIEW score_table AS ( SELECT stuName, SUM(CASE project WHEN '语文' THEN stuScore ELSE 0 END) AS 语文, SUM(CASE project WHEN '数学' THEN stuScore ELSE 0 END) AS 数学, SUM(CASE project WHEN '英语' THEN stuScore ELSE 0 END) AS 英语 FROM `t_score` GROUP BY stuName ) SELECT * FROM score_table DROP VIEW score_table