[面试]应聘某公司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 NAMEORDER 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>