创建视图

竖行变横行的查询

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