[面试]应聘某公司BI/DW/SQL开发的一道测试题（行转列）

题目：

Provide:

NAME	COURSE	SCORE
Alex	English	81
Lucy	Math	79
Lucy	English	82
Alex	English	83
Alex	Math	77
Lucy	Math	75

Expect：

NAME	ENGLISH	MATH	SUM_SCORE
Alex	83	77	160
Lucy	82	79	161

我的理解：取每个人每科最大分，再计算总分。

实现SQL（原来word直接贴过来的复制再粘贴后空格丢失，改成嵌到代码框中的形式了）:

SELECT   NAME
        ,SUM(english) english
        ,SUM(math) math
        ,SUM(english+ math)sum_score
    FROM(SELECT   NAME
                 ,MAX(score) english
                 ,0 math
             FROM class_score
            WHERE course ='English'
          GROUP BY NAME
          UNION
          SELECT   NAME
                 ,0 english
                 ,MAX(score) math
             FROM class_score
            WHERE course ='Math'
          GROUP BY NAME)
GROUP BY NAME
ORDER BY NAME

测试：

CREATE TABLE class_score(
NAME VARCHAR2(20),
course VARCHAR2(20),
score NUMBER);


INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Alex', 'English', 81);

INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Lucy', 'Math', 79);

INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Lucy', 'English', 82);

INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Alex', 'English', 83);

INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Alex', 'Math', 77);

INSERT INTO class_score
(NAME
,course
,score
)
VALUES ('Lucy', 'Math', 75);

SQL> col name for a10
SQL> col course for a10
SQL> col score for 99
SQL> SELECT *
2 FROM class_score;

NAME COURSE SCORE
---------- ---------- -----
Alex English 81
Lucy Math 79
Lucy English 82
Alex English 83
Alex Math 77
Lucy Math 75

6 rows selected.

SQL> SELECT NAME
2 ,SUM (english) english
3 ,SUM (math) math
4 ,SUM (english + math) sum_score
5 FROM (SELECT NAME
6 ,MAX (score) english
7 ,0 math
8 FROM class_score
9 WHERE course = 'English'
10 GROUP BY NAME
11 UNION
12 SELECT NAME
13 ,0 english
14 ,MAX (score) math
15 FROM class_score
16 WHERE course = 'Math'
17 GROUP BY NAME)
18 GROUP BY NAME
19 ORDER BY NAME
20 /

NAME ENGLISH MATH SUM_SCORE
---------- ---------- ---------- ----------
Alex 83 77 160
Lucy 82 79 161

SQL>