【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程式設計師面試筆試寶典》，作者：小麥苗。
