06. 图表

-- 语文
CREATE VIEW v_chinese AS SELECT sid, score AS chinese
FROM (
         SELECT *,
                if(subject = 'chinese', 1, 0) AS is_c
         FROM test
     ) AS chinese_score
WHERE is_c = 1;
-- 数学
CREATE VIEW v_math AS SELECT sid, score AS math
FROM (
         SELECT *,
                if(subject = 'math', 1, 0) AS is_m
         FROM test
     ) AS math_score
WHERE is_m = 1;
-- 英语
CREATE VIEW v_english AS SELECT sid, score AS english
FROM (
         SELECT *,
                if(subject = 'english', 1, 0) AS is_e
         FROM test
     ) AS eng_score
WHERE is_e = 1;
-- 连接
SELECT
       c.sid,
       chinese,
       math,
       english
FROM v_chinese AS c
    INNER JOIN v_math AS m ON c.sid = m.sid
    INNER JOIN v_english AS e ON c.sid = e.sid;