Oracle 字段格式显示

来源：互联网发布：ip和mac绑定编辑：程序博客网时间：2024/05/06 02:12

建表语句（ORACL）（含有记录）

-- CREATE TABLECREATE 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 SGROUP BY S.STUDENTNAME