oracle字段显示,Oracle 字段格式显示

建表语句(ORACL)(含有记录)

-- CREATE TABLE

CREATE TABLE STUDENT

(

STUDENTID NUMBER(10),

STUDENTNAME VARCHAR2(30),

STUDENTSCORES NUMBER(3)

)

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1001, '李四', 100);

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1002, '李四', 125);

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1003, '李四', 98);

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1001, '张三', 100);

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1002, '张三', 125);

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)

VALUES (1003, '张三', 99);

原始显示

要求这么显示

注：其中，STUDENTID(1001、1002、1003)分别代表 语、数、外；STUDENTNAME是姓名；STUDENTSCORES是学分。

答案

SELECT S.STUDENTNAME AS "姓名" ,

MAX(DECODE(S.STUDENTID,1001,S.STUDENTSCORES,0)) AS "语文",

MAX(DECODE(S.STUDENTID,1002,S.STUDENTSCORES,0)) AS "数学",

MAX(DECODE(S.STUDENTID,1003,S.STUDENTSCORES,0)) AS "英语"

FROM STUDENT S

GROUP BY S.STUDENTNAME