【DB笔试面试467】Oracle中行列互换有哪些方法？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
Oracle中行列互换有哪些方法？
♣
答案部分
行列转换包括以下六种情况：（1）列转行。（2）行转列。（3）多列转换成字符串。（4）多行转换成字符串。（5）字符串转换成多列。（6）字符串转换成多行。其中，重点是行转列和字符串转换成多行。
下面将分别对这几种情况举例来说明。
1、列转行
列转行就是将原表中的列名作为转换后的表的内容。列转行主要采用UNION ALL来完成。示例代码如下所示：
CREATE TABLE TEST_LHR ( NAME VARCHAR2(255), JANUARY NUMBER(18), FEBRUARY NUMBER(18), MARCH NUMBER(18), APRIL NUMBER(18), MAY NUMBER(18) ); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('长寿', 58, 12, 26, 18, 269); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('璧山', 33, 18, 17, 16, 206); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('杨家坪', 72, 73, 79, 386, 327); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('巫溪', 34, 9, 7, 21, 33); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('丰都', 62, 46, 39, 36, 91); INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY) VALUES ('武隆', 136, 86, 44, 52, 142); COMMIT; SELECT * FROM TEST_LHR;
查询结果如下所示：
下面进行列转换：
SELECT * FROM (SELECT T.NAME, 'JANUARY' MONTH, T.JANUARY V_NUM FROM TEST_LHR T UNION ALL SELECT T.NAME, 'FEBRUARY' MONTH, T.FEBRUARY V_NUM FROM TEST_LHR T UNION ALL SELECT T.NAME, 'MARCH' MONTH, T.MARCH V_NUM FROM TEST_LHR T UNION ALL SELECT T.NAME, 'APRIL' MONTH, T.APRIL V_NUM FROM TEST_LHR T UNION ALL SELECT T.NAME, 'MAY' MONTH, T.MAY V_NUM FROM TEST_LHR T) ORDER BY NAME;
查询结果如下所示：
列转行也可以使用unpivot函数，如下所示：
drop table test purge; create table test as SELECT * FROM (SELECT e.deptno, e.sal FROM scott.emp e) pivot(COUNT(*) AS cnt, SUM(sal) AS s FOR deptno IN(10 AS d10, 20 AS d20, 30 AS d30)) ORDER BY 1; SELECT * FROM test;
查询结果：
SELECT * FROM test unpivot(人次 FOR deptno IN(d10_cnt, d20_cnt, d30_cnt));
查询结果如下所示：
SELECT deptno AS 部门编码, 人次, 工资 FROM test a unpivot include nulls (人次 FOR deptno IN(d10_cnt as 10, d20_cnt as 20, d30_cnt as 30)) unpivot include nulls (工资 FOR deptno2 IN(d10_s as 10, d20_s as 20, d30_s as 30)) where deptno= deptno2 ;
查询结果如下所示：
2、行转列
行转列就是将行数据内容作为列名。示例代码如下所示：
CREATE TABLE T_ROW_COL_LHR( NUM VARCHAR2(15 CHAR), NAME VARCHAR2(20 CHAR), SEX VARCHAR2(2 CHAR), CLASSES VARCHAR2(30 CHAR), COURSE_NAME VARCHAR2(50 CHAR) ); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206211','王艺','男','06-1班','保险学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','保险学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务管理'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务会计'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','电子商务'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公共经济学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公司理财'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','管理学原理'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','保险学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','保险学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务管理'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务会计'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','电子商务'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','公共经济学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','环境管理学'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','管理学原理'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','商务谈判'); INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206216','李佳琪','男','06-2','土地估计'); SELECT * FROM T_ROW_COL_LHR;
查询结果如下所示：
将COURSE_NAME进行行转列：
SELECT NUM,NAME,SEX,CLASSES, MAX(DECODE(RN,1,COURSE_NAME,NULL)) COURSE_NAME_1, MAX(DECODE(RN,2,COURSE_NAME,NULL)) COURSE_NAME_2, MAX(DECODE(RN,3,COURSE_NAME,NULL)) COURSE_NAME_3, MAX(DECODE(RN,4,COURSE_NAME,NULL)) COURSE_NAME_4, MAX(DECODE(RN,5,COURSE_NAME,NULL)) COURSE_NAME_5 FROM (SELECT NUM,NAME,SEX,CLASSES,COURSE_NAME, ROW_NUMBER() OVER(PARTITION BY NUM,NAME,SEX,CLASSES ORDER BY COURSE_NAME) RN FROM T_ROW_COL_LHR) GROUP BY NUM,NAME,SEX,CLASSES;
结果如下所示：
将COURSE_NAME列合并，示例代码如下所示：
SELECT NUM, NAME, SEX, CLASSES, (MAX(DECODE(RN, 1, COURSE_NAME, NULL)) || MAX(DECODE(RN, 2, ',' || COURSE_NAME, NULL)) || MAX(DECODE(RN, 3, ',' || COURSE_NAME, NULL)) || MAX(DECODE(RN, 4, ',' || COURSE_NAME, NULL)) || MAX(DECODE(RN, 5, ',' || COURSE_NAME, NULL))) NAME FROM (SELECT NUM, NAME, SEX, CLASSES, COURSE_NAME, ROW_NUMBER() OVER(PARTITION BY NUM, NAME, SEX, CLASSES ORDER BY COURSE_NAME) RN FROM T_ROW_COL_LHR) GROUP BY NUM, NAME, SEX, CLASSES;
也可以使用LISTAGG函数直接转换：
SELECT NUM,NAME, SEX, CLASSES, LISTAGG(COURSE_NAME, ',') WITHIN GROUP(ORDER BY COURSE_NAME DESC) FROM T_ROW_COL_LHR GROUP BY NUM,NAME, SEX, CLASSES;
也可以使用VM_CONCAT函数直接转换：
SELECT NUM,NAME, SEX, CLASSES, WM_CONCAT(COURSE_NAME) FROM T_ROW_COL_LHR GROUP BY NUM,NAME, SEX, CLASSES;
结果如下所示：
3、多列转换成字符串
使用||或CONCAT函数实现，示例代码如下所示：
SELECT CONCAT('A','B') FROM DUAL;
4、多行转换成字符串
示例代码如下所示：
CREATE TABLE T_ROW_STR( ID INT, COL VARCHAR2(10) ); INSERT INTO T_ROW_STR VALUES(1,'A'); INSERT INTO T_ROW_STR VALUES(1,'B'); INSERT INTO T_ROW_STR VALUES(1,'C'); INSERT INTO T_ROW_STR VALUES(2,'A'); INSERT INTO T_ROW_STR VALUES(2,'D'); INSERT INTO T_ROW_STR VALUES(2,'E'); INSERT INTO T_ROW_STR VALUES(3,'C'); COMMIT; SELECT * FROM T_ROW_STR;
查询结果如下所示：
下面进行转换：
SELECT ID, MAX(DECODE(RN, 1, COL, NULL)) || MAX(DECODE(RN, 2, ',' || COL, NULL)) || MAX(DECODE(RN, 3, ',' || COL, NULL)) STR FROM (SELECT ID, COL, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN FROM T_ROW_STR) T GROUP BY ID ORDER BY 1;
也可以使用SYS_CONNECT_BY_PATH来实现：
SELECT T.ID ID, MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.COL, ','), 2)) STR FROM (SELECT ID, COL, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) RN FROM T_ROW_STR) T START WITH RN = 1 CONNECT BY RN = PRIOR RN + 1 AND ID = PRIOR ID GROUP BY T.ID;
也可以使用LISTAGG函数直接转换：
SELECT t.id, LISTAGG(t.col,',') WITHIN GROUP (ORDER BY t.id desc ) FROM T_ROW_STR t GROUP BY t.id;
也可以使用VM_CONCAT函数直接转换：
SELECT t.id, WM_CONCAT(t.col) FROM T_ROW_STR t GROUP BY t.id;
查询结果如下所示：
5、字符串转换成多列
实际上就是一个字符串拆分的问题。示例代码如下所示：
CREATE TABLE T_COL_ROW( ID INT, C1 VARCHAR2(10), C2 VARCHAR2(10), C3 VARCHAR2(10)); INSERT INTO T_COL_ROW VALUES (1, 'v11', 'v21', 'v31'); INSERT INTO T_COL_ROW VALUES (2, 'v12', 'v22', NULL); INSERT INTO T_COL_ROW VALUES (3, 'v13', NULL, 'v33'); INSERT INTO T_COL_ROW VALUES (4, NULL, 'v24', 'v34'); INSERT INTO T_COL_ROW VALUES (5, 'v15', NULL, NULL); INSERT INTO T_COL_ROW VALUES (6, NULL, NULL, 'v35'); INSERT INTO T_COL_ROW VALUES (7, NULL, NULL, NULL); COMMIT; SELECT * FROM T_COL_ROW; CREATE TABLE T_STR_COL AS SELECT ID,C1||','||C2||','||C3 AS C123 FROM T_COL_ROW; SELECT * FROM T_STR_COL;
查询结果如下所示：
下面进行转换：
SELECT ID, C123, SUBSTR(C123, 1, INSTR(C123 || ',', ',', 1, 1) - 1) C1, SUBSTR(C123, INSTR(C123 || ',', ',', 1, 1) + 1, INSTR(C123 || ',', ',', 1, 2) - INSTR(C123 || ',', ',', 1, 1) - 1) C2, SUBSTR(C123, INSTR(C123 || ',', ',', 1, 2) + 1, INSTR(C123 || ',', ',', 1, 3) - INSTR(C123 || ',', ',', 1, 2) - 1) C3 FROM T_STR_COL ORDER BY 1;
查询结果如下所示：
6、字符串转换成多行
示例代码如下所示：
CREATE TABLE T_STR_ROW AS SELECT ID, MAX(DECODE(RN, 1, COL, NULL)) || MAX(DECODE(RN, 2, ',' || COL, NULL)) || MAX(DECODE(RN, 3, ',' || COL, NULL)) STR FROM (SELECT ID, COL, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN FROM T_ROW_STR) T GROUP BY ID ORDER BY 1; SELECT * FROM T_STR_ROW;
查询结果如下所示：
转换代码如下：
SELECT ID, 1 AS P, SUBSTR(STR, 1, INSTR(STR || ',', ',', 1, 1) - 1) AS CV FROM T_STR_ROW UNION ALL SELECT ID, 2 AS P, SUBSTR(STR, INSTR(STR || ',', ',', 1, 1) + 1, INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV FROM T_STR_ROW UNION ALL SELECT ID, 3 AS P, SUBSTR(STR, INSTR(STR || ',', ',', 1, 1) + 1, INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV FROM T_STR_ROW ORDER BY 1, 2;
查询结果如下所示：
还有几类特殊的转换，如下所示：
CREATE OR REPLACE TYPE INS_SEQ_TYPE IS VARRAY(8) OF NUMBER; SELECT * FROM TABLE(INS_SEQ_TYPE(1, 2, 3, 4, 5));
结果：
COLUMN_VALUE ------------ 1 2 3 4 5
若是字符串类型，则如下所示：
CREATE OR REPLACE TYPE INS_SEQ_TYPE2 IS VARRAY(80) OF VARCHAR2(32767); SELECT * FROM TABLE(INS_SEQ_TYPE2('aadf,dea','cbc','d'));
结果：
COLUMN_VALUE ----------------- aadf,dea cbc d
还有如下的形式：
先创建一个TYPE类型，代码如下：
CREATE OR REPLACE TYPE TYPE_STR_LHR IS TABLE OF VARCHAR2(32767);
再创建FUN_SPLIT2_LHR函数，代码如下：
CREATE OR REPLACE FUNCTION FUN_SPLIT2_LHR(P_STR VARCHAR2, V_SPLIT VARCHAR2 DEFAULT ',') RETURN TYPE_STR_LHR IS RS TYPE_STR_LHR := TYPE_STR_LHR(); V_STR VARCHAR2(4000) := ''; V_LEN NUMBER := 0; BEGIN V_STR := P_STR; V_LEN := LENGTH(V_SPLIT); WHILE LENGTH(V_STR) > 0 LOOP IF INSTR(V_STR, V_SPLIT) > 0 THEN RS.EXTEND; RS(RS.COUNT) := SUBSTR(V_STR, 1, INSTR(V_STR, V_SPLIT) - 1); V_STR := SUBSTR(V_STR, INSTR(V_STR, V_SPLIT) + V_LEN); ELSE RS.EXTEND; RS(RS.COUNT) := V_STR; EXIT; END IF; END LOOP; RETURN RS; END;
测试如下：
SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101,102,103',',')); COLUMN_VALUE ------------------ 101 102 103 SQL> SELECT TO_NUMBER(COLUMN_VALUE) FROM TABLE(FUN_SPLIT2_LHR('101,102,103')); TO_NUMBER(COLUMN_VALUE) ----------------------- 101 102 103 SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101@#102@#103','@#')); COLUMN_VALUE --------------- 101 102 103
& 说明：
有关行列互换更多的案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1272538/
真题1、数据库中有一张如下所示的表，表名为SALES。
|
年 |
季度 |
销售量 |
|---|---|---|
|
1991 |
1 |
11 |
|
1991 |
2 |
12 |
|
1991 |
3 |
13 |
|
1991 |
4 |
14 |
|
1992 |
1 |
21 |
|
1992 |
2 |
22 |
|
1992 |
3 |
23 |
|
1992 |
4 |
24 |
要求：写一个SQL语句查询出如下所示的结果。
|
年 |
一季度 |
二季度 |
三季度 |
四季度 |
|---|---|---|---|---|
|
1991 |
11 |
12 |
13 |
14 |
|
1992 |
21 |
22 |
23 |
24 |
答案：这是一道行转列的题目，首先建立表SALES：
CREATE TABLE SALES(年 NUMBER,季度 NUMBER,销售量 NUMBER); INSERT INTO SALES VALUES(1991, 1 ,11); INSERT INTO SALES VALUES(1991, 2 ,12); INSERT INTO SALES VALUES(1991, 3 ,13); INSERT INTO SALES VALUES(1991, 4 ,14); INSERT INTO SALES VALUES(1992, 1 ,21); INSERT INTO SALES VALUES(1992, 2 ,22); INSERT INTO SALES VALUES(1992, 3 ,23); INSERT INTO SALES VALUES(1992, 4 ,24); SELECT * FROM SALES;
此题若使用聚合函数+DECODE或CASE来回答，如下所示：
SELECT 年, SUM(CASE WHEN 季度=1 THEN 销售量 ELSE 0 END) AS 一季度, SUM(CASE WHEN 季度=2 THEN 销售量 ELSE 0 END) AS 二季度, SUM(CASE WHEN 季度=3 THEN 销售量 ELSE 0 END) AS 三季度, SUM(CASE WHEN 季度=4 THEN 销售量 ELSE 0 END) AS 四季度 FROM SALES GROUP BY 年 ORDER BY T.教师号;
此题若使用PIVOT函数，如下所示：
SELECT * FROM SALES PIVOT(SUM(销售量) FOR 季度 IN(1 AS "一季度", 2 AS "二季度", 3 AS "三季度", 4 AS "四季度")) ORDER BY 1;
此题若使用临时表的方式，如下所示：
SELECT T.年, NVL(SUM(T1.一季度),0) AS "一季度", NVL(SUM(T2.二季度),0) AS "二季度", NVL(SUM(T3.三季度),0) AS "三季度", NVL(SUM(T4.四季度),0) AS "四季度" FROM (SELECT 年,销售量 AS "一季度" FROM SALES A WHERE A.季度 = '1') T1, (SELECT 年,销售量 AS "二季度" FROM SALES A WHERE A.季度 = '2') T2, (SELECT 年,销售量 AS "三季度" FROM SALES A WHERE A.季度 = '3') T3, (SELECT 年,销售量 AS "四季度" FROM SALES A WHERE A.季度 = '4') T4, (SELECT DISTINCT 年 FROM SALES) T WHERE T.年 = T1.年(+) AND T.年 = T2.年(+) AND T.年 = T3.年(+) AND T.年 = T4.年(+) GROUP BY T.年 ORDER BY 1;
真题2、有如下的表格：
create table test( id number(10) primary key, type number(10) , t_id number(10), value varchar2(6) ); insert into test values(100,1,1,'张三'); insert into test values(200,2,1,'男'); insert into test values(300,3,1,'50'); insert into test values(101,1,2,'刘二'); insert into test values(201,2,2,'男'); insert into test values(301,3,2,'30'); insert into test values(102,1,3,'刘三'); insert into test values(202,2,3,'女'); insert into test values(302,3,3,'10'); select * from test;
查询结果如下所示：
根据以上代码生成的表写出一条查询语句，查询结果如下：
姓名 性别 年龄 张三 男 50 刘二 男 30 刘三 女 10
答案：根据表格可以分析出TYPE列中1代表姓名、2代表性别、3代表年龄，而T_ID中ID一样的为同一个人的属性。查询结果中列依次为姓名、性别、年龄，而TYPE列决定姓名、性别、年龄。
方法一：使用分组，先对T_ID进行分组，然后用DECODE函数过滤数据：
SELECT MAX(decode(TYPE, 1, VALUE)) "姓名", MAX(decode(TYPE, 2, VALUE)) "性别", MAX(decode(TYPE, 3, VALUE)) "年龄" FROM test GROUP BY t_id;
方法二：使用连表，通过WHERE过滤生成3张TYPE分别等于1（姓名）、2（性别）、3（年龄）的3张虚拟表，再通过WHERE连接条件三张表T_ID相等的为同一个人或者说同一条记录（行）：
SELECT t1.value "姓名", t2.value "性别", t3.value "年龄" FROM (SELECT VALUE, t_id FROM test WHERE TYPE = 1) t1, (SELECT VALUE, t_id FROM test WHERE TYPE = 2) t2, (SELECT VALUE, t_id FROM test WHERE TYPE = 3) t3 WHERE t1.t_id = t2.t_id AND t1.t_id = t3.t_id;
结果如下所示：
真题3、有如下表内容：
2005-05-09 胜 2005-05-09 胜 2005-05-09 负 2005-05-09 负 2005-05-10 胜 2005-05-10 负 2005-05-10 负
如果要生成下列结果，那么该如何编写SQL语句？
胜 负 2005-05-09 2 2 2005-05-10 1 2
其中，建表语句如下所示：
create table tmp(rq varchar2(10),shengfu varchar2(5)); insert into tmp values('2005-05-09','胜'); insert into tmp values('2005-05-09','胜'); insert into tmp values('2005-05-09','负'); insert into tmp values('2005-05-09','负'); insert into tmp values('2005-05-10','胜'); insert into tmp values('2005-05-10','负'); insert into tmp values('2005-05-10','负'); select * from tmp;
答案：方法一：使用分组。按日期分组，用conut函数计算次数。
SELECT rq "日期", COUNT(decode(shengfu, '胜', 1)) "胜", COUNT(decode(shengfu, '负', 1)) "负" FROM tmp GROUP BY rq ORDER BY rq;
方法二：使用连表。
SELECT t1.rq, t1.胜, t2.负 FROM (SELECT COUNT(decode(shengfu, '胜', 1)) "胜", rq FROM tmp GROUP BY rq) t1 JOIN (SELECT COUNT(decode(shengfu, '负', 1)) "负", rq FROM tmp GROUP BY rq) t2 ON t1.rq = t2.rq;
真题4、有如下的表：
create table STUDENT_SCORE ( name VARCHAR2(20), subject VARCHAR2(20), score NUMBER(4,1) ); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0); insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0); insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0); insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0); select * from STUDENT_SCORE;
表STUDENT_SCORE中数据：
如要得到类似下面的结果，那么该如何编写SQL语句：
答案：方法一：使用分组
SELECT NAME "姓名", MAX(decode(subject, '语文' ，score)) "语文", MAX(decode(subject, '数学' ，score)) "数学", MAX(decode(subject, '英语' ，score)) "英语" FROM STUDENT_SCORE GROUP BY NAME; 方法二：使用连表 SELECT t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 FROM (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '语文') t1 JOIN (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '数学') t2 ON t1.name = t2.name JOIN (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '英语') t3 ON t1.name = t3.name;
如果大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格，那么请继续给出SQL语句：
SELECT t.姓名, (CASE WHEN t.语文 >= 80 THEN '优秀' WHEN t.语文 >= 60 THEN '及格' ELSE '不及格' END) 语文, (CASE WHEN t.数学 >= 80 THEN '优秀' WHEN t.数学 >= 60 THEN '及格' ELSE '不及格' END) 数学, (CASE WHEN t.英语 >= 80 THEN '优秀' WHEN t.英语 >= 60 THEN '及格' ELSE '不及格' END) 英语 FROM (SELECT t1.name 姓名, t1.score 语文, t2.score 数学, t3.score 英语 FROM (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '语文') t1 JOIN (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '数学') t2 ON t1.name = t2.name JOIN (SELECT NAME, score FROM STUDENT_SCORE WHERE subject = '英语') t3 ON t1.name = t3.name) t;
结果：
真题5、有如下2张表：
yj01表：
月份mon 部门dep 业绩yj ----------------------- 一月份 1 10 一月份 2 10 一月份 3 5 二月份 2 8 二月份 4 9 三月份 3 8
yjdept表：
部门dep 部门名称dname -------------------- 1 国内业务一部 2 国内业务二部 3 国内业务三部 4 国际业务部
建表语句分别如下所示：
create table yj01( month varchar2(10), deptno number(10), yj number(10) ); insert into yj01(month,deptno,yj) values('一月份',1,10); insert into yj01(month,deptno,yj) values('二月份',2,10); insert into yj01(month,deptno,yj) values('二月份',3,5); insert into yj01(month,deptno,yj) values('三月份',2,8); insert into yj01(month,deptno,yj) values('三月份',4,9); insert into yj01(month,deptno,yj) values('三月份',3,8); create table yjdept( deptno number(10), dname varchar2(20) ); insert into yjdept(deptno,dname) values(1,'国内业务一部'); insert into yjdept(deptno,dname) values(2,'国内业务二部'); insert into yjdept(deptno,dname) values(3,'国内业务三部'); insert into yjdept(deptno,dname) values(4,'国际业务部'); select * from yj01; select * from yjdept;
请按照月份和部门进行分组计算。
答案：使用分组：
SELECT deptno, MAX(decode(MONTH, '一月份', yj)) 一月份, MAX(decode(MONTH, '二月份', yj)) 二月份, MAX(decode(MONTH, '三月份', yj)) 三月份 FROM yj01 GROUP BY deptno ORDER BY deptno;
使用连接：
SELECT t1.deptno, t1.yj 一月份, t2.yj 二月份, t3.yj 三月份 FROM (SELECT y2.deptno, y1.yj FROM (SELECT yj, deptno FROM yj01 WHERE MONTH = '一月份') y1 RIGHT JOIN yjdept y2 ON y1.deptno = y2.deptno) t1 JOIN (SELECT y2.deptno, y1.yj FROM (SELECT yj, deptno FROM yj01 WHERE MONTH = '二月份') y1 RIGHT JOIN yjdept y2 ON y1.deptno = y2.deptno) t2 ON t1.deptno = t2.deptno JOIN (SELECT y2.deptno, y1.yj FROM (SELECT yj, deptno FROM yj01 WHERE MONTH = '三月份') y1 RIGHT JOIN yjdept y2 ON y1.deptno = y2.deptno) t3 ON t1.deptno = t3.deptno ORDER BY t1.deptno;
结果：
真题6、有表T1，其数据如下所示：
STUDENT SUBJECT GRADE --------------------------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100
现需要转换为如下的形式：
student 语文 数学 英语 student1 80 70 60 student2 90 80 100
试写出其SQL语句。
答案：该题属于固定列数的行转列，环境如下：
DROP TABLE T1; CREATE TABLE T1(STUDENT VARCHAR2(30),SUBJECT VARCHAR2(30) ,GRADE NUMBER); INSERT INTO T1 VALUES('student1', '语文', 80 ); INSERT INTO T1 VALUES('student1', '数学', 70 ); INSERT INTO T1 VALUES('student1', '英语', 60 ); INSERT INTO T1 VALUES('student2', '语文', 90 ); INSERT INTO T1 VALUES('student2', '数学', 80 ); INSERT INTO T1 VALUES('student2', '英语', 100); SELECT * FROM t1;
若采用MAX或CASE+DECODE或CASE来回答，如下所示：
SELECT STUDENT, SUM(DECODE(SUBJECT, '语文', GRADE, NULL)) "语文", SUM(DECODE(SUBJECT, '数学', GRADE, NULL)) "数学", SUM(DECODE(SUBJECT, '英语', GRADE, NULL)) "英语" FROM T1 GROUP BY STUDENT;
此题若使用PIVOT函数，如下所示：
SELECT * FROM T1 PIVOT(SUM(GRADE) FOR SUBJECT IN('语文' AS "语文", '数学' AS "数学", '英语' AS "英语")) ORDER BY 1;
此题若使用临时表的方式，如下所示：
SELECT T.STUDENT, NVL(SUM(T1.语文),0) AS "语文", NVL(SUM(T2.数学),0) AS "数学", NVL(SUM(T3.英语),0) AS "英语" FROM (SELECT STUDENT,GRADE AS "语文" FROM T1 A WHERE A.SUBJECT = '语文') T1, (SELECT STUDENT,GRADE AS "数学" FROM T1 A WHERE A.SUBJECT = '数学') T2, (SELECT STUDENT,GRADE AS "英语" FROM T1 A WHERE A.SUBJECT = '英语') T3, (SELECT DISTINCT STUDENT FROM T1) T WHERE T.STUDENT = T1.STUDENT(+) AND T.STUDENT = T2.STUDENT(+) AND T.STUDENT = T3.STUDENT(+) GROUP BY T.STUDENT ORDER BY 1;
真题7、有表T1结构如下：
c1 c2 ---- --------- 1 我 1 是 1 谁 2 知 2 道 3 不 ……
现需要转换为如下形式：
1 我是谁 2 知道 3 不
试写出其SQL语句。
答案：此题可以使用listagg函数或者定制的WM_CONCAT函数（参考：【DB笔试面试459】ORA-00904: "wm_concat":invalid identifier错误如何解决？）来实现。listagg函数如下所示：
WITH TEMP AS( SELECT '1' c1 ,'我' c2 FROM DUAL UNION ALL SELECT '1' c1 ,'是' c2 FROM DUAL UNION ALL SELECT '1' c1 ,'谁' c2 FROM DUAL UNION ALL SELECT '2' c1 ,'知' c2 FROM DUAL UNION ALL SELECT '2' c1 ,'道' c2 FROM DUAL UNION ALL SELECT '3' c1 ,'不' c2 FROM DUAL ) SELECT c1,LISTAGG(c2,'') WITHIN GROUP (ORDER BY c2 desc ) FROM TEMP GROUP BY c1;
另外，也可以借助于PL/SQL来完成，这里给一个简单的例子，其中默认原表名为T：
CREATE OR REPLACE FUNCTION GET_C2(TMP_C1 NUMBER) RETURN VARCHAR2 IS COL_C2 VARCHAR2(4000); BEGIN FOR CUR IN (SELECT C2 FROM T WHERE C1 = TMP_C1) LOOP COL_C2 := COL_C2 || CUR.C2; END LOOP; COL_C2 := RTRIM(COL_C2, 1); RETURN COL_C2; END; / SQL> SELECT DISTINCT C1 ,GET_C2(C1) CC2 FROM TABLE;
& 说明：
有关行列互换更多的案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1272538/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗。
