Oracle中行列互换有哪些方法？

DB宝 2019-05-17
2333
♣题目部分Oracle中行列互换有哪些方法？

     
♣答案部分

行列转换包括以下六种情况：（1）列转行。（2）行转列。（3）多列转换成字符串。（4）多行转换成字符串。（5）字符串转换成多列。（6）字符串转换成多行。其中，重点是行转列和字符串转换成多行。
下面将分别对这几种情况举例来说明。
1、列转行
列转行就是将原表中的列名作为转换后的表的内容。列转行主要采用UNION ALL来完成。示例代码如下所示：
 1CREATE TABLE TEST_LHR
 2(
 3  NAME     VARCHAR2(255),
 4  JANUARY  NUMBER(18),
 5  FEBRUARY NUMBER(18),
 6  MARCH    NUMBER(18),
 7  APRIL    NUMBER(18),
 8  MAY      NUMBER(18)
 9);
10INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
11VALUES ('长寿', 58, 12, 26, 18, 269);
12INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
13VALUES ('璧山', 33, 18, 17, 16, 206);
14INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
15VALUES ('杨家坪', 72, 73, 79, 386, 327);
16INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
17VALUES ('巫溪', 34, 9, 7, 21, 33);
18INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
19VALUES ('丰都', 62, 46, 39, 36, 91);
20INSERT INTO TEST_LHR (NAME, JANUARY, FEBRUARY, MARCH, APRIL, MAY)
21VALUES ('武隆', 136, 86, 44, 52, 142);
22COMMIT;
23SELECT * FROM TEST_LHR;

查询结果如下所示：

下面进行列转换：
 1SELECT *
 2  FROM (SELECT T.NAME, 'JANUARY' MONTH, T.JANUARY V_NUM
 3          FROM TEST_LHR T
 4        UNION ALL
 5        SELECT T.NAME, 'FEBRUARY' MONTH, T.FEBRUARY V_NUM
 6          FROM TEST_LHR T
 7        UNION ALL
 8        SELECT T.NAME, 'MARCH' MONTH, T.MARCH V_NUM
 9          FROM TEST_LHR T
10        UNION ALL
11        SELECT T.NAME, 'APRIL' MONTH, T.APRIL V_NUM
12          FROM TEST_LHR T
13        UNION ALL
14        SELECT T.NAME, 'MAY' MONTH, T.MAY V_NUM
15          FROM TEST_LHR T)
16 ORDER BY NAME;


 查询结果如下所示：
 
列转行也可以使用unpivot函数，如下所示：
 1drop table test purge;
 2create table test as 
 3SELECT *
 4FROM   (SELECT e.deptno,
 5               e.sal 
 6        FROM   scott.emp e)
 7pivot(COUNT(*) AS cnt, SUM(sal) AS s
 8FOR    deptno IN(10 AS d10, 20 AS d20, 30 AS d30))
 9ORDER  BY 1;
10
11SELECT * FROM test;

查询结果：

1SELECT * FROM test unpivot(人次 FOR deptno IN(d10_cnt, d20_cnt, d30_cnt));

查询结果如下所示：
1SELECT deptno AS 部门编码,
2       人次,
3       工资
4FROM   test a 
5unpivot include nulls  (人次 FOR deptno IN(d10_cnt as 10, d20_cnt as 20, d30_cnt as 30))  
6unpivot include nulls  (工资 FOR deptno2 IN(d10_s as 10, d20_s as 20, d30_s as 30))
7 where  deptno= deptno2 
8;

查询结果如下所示：


2、行转列
行转列就是将行数据内容作为列名。示例代码如下所示：
 1CREATE TABLE T_ROW_COL_LHR(
 2NUM VARCHAR2(15 CHAR),
 3NAME VARCHAR2(20 CHAR),
 4SEX VARCHAR2(2 CHAR),
 5CLASSES VARCHAR2(30 CHAR),
 6COURSE_NAME VARCHAR2(50 CHAR) 
 7);
 8
 9INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME)  VALUES ('206211','王艺','男','06-1班','保险学');
10INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','保险学');
11INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务管理');
12INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206212','肖薇','女','06-2','财务会计');
13INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','电子商务');
14INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公共经济学');
15INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','公司理财');
16INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','管理学原理');
17INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206213','陈雅诗','女','06-2','保险学');
18INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','保险学');
19INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务管理');
20INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','财务会计');
21INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','电子商务');
22INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206214','李丹阳','男','06-1','公共经济学');
23INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','环境管理学');
24INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','管理学原理');
25INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206215','杨伊琳','女','06-3班','商务谈判');
26INSERT INTO T_ROW_COL_LHR(NUM,NAME,SEX,CLASSES,COURSE_NAME) VALUES ('206216','李佳琪','男','06-2','土地估计');
27SELECT * FROM T_ROW_COL_LHR;

查询结果如下所示：
 
将COURSE_NAME进行行转列：
 1SELECT NUM,NAME,SEX,CLASSES,
 2            MAX(DECODE(RN,1,COURSE_NAME,NULL)) COURSE_NAME_1,
 3            MAX(DECODE(RN,2,COURSE_NAME,NULL)) COURSE_NAME_2,
 4            MAX(DECODE(RN,3,COURSE_NAME,NULL)) COURSE_NAME_3,
 5            MAX(DECODE(RN,4,COURSE_NAME,NULL)) COURSE_NAME_4,
 6            MAX(DECODE(RN,5,COURSE_NAME,NULL)) COURSE_NAME_5
 7FROM   (SELECT NUM,NAME,SEX,CLASSES,COURSE_NAME,
 8ROW_NUMBER() OVER(PARTITION BY NUM,NAME,SEX,CLASSES ORDER BY COURSE_NAME) RN
 9                   FROM   T_ROW_COL_LHR)
10GROUP  BY NUM,NAME,SEX,CLASSES;

结果如下所示：
将COURSE_NAME列合并，示例代码如下所示：
 1SELECT NUM,
 2       NAME,
 3       SEX,
 4       CLASSES,
 5       (MAX(DECODE(RN, 1, COURSE_NAME, NULL)) ||
 6       MAX(DECODE(RN, 2, ',' || COURSE_NAME, NULL)) ||
 7       MAX(DECODE(RN, 3, ',' || COURSE_NAME, NULL)) ||
 8       MAX(DECODE(RN, 4, ',' || COURSE_NAME, NULL)) ||
 9       MAX(DECODE(RN, 5, ',' || COURSE_NAME, NULL))) NAME
10FROM   (SELECT NUM,
11               NAME,
12               SEX,
13               CLASSES,
14               COURSE_NAME,
15               ROW_NUMBER() OVER(PARTITION BY NUM, NAME, SEX, CLASSES ORDER BY COURSE_NAME) RN
16        FROM   T_ROW_COL_LHR)
17GROUP  BY NUM,
18          NAME,
19          SEX,
20          CLASSES;

也可以使用LISTAGG函数直接转换：
1SELECT NUM,NAME,
2       SEX,
3       CLASSES,
4       LISTAGG(COURSE_NAME, ',') WITHIN GROUP(ORDER BY COURSE_NAME DESC)
5FROM   T_ROW_COL_LHR
6GROUP  BY NUM,NAME,
7          SEX,
8          CLASSES;

也可以使用VM_CONCAT函数直接转换：
1SELECT NUM,NAME,
2       SEX,
3       CLASSES,
4       WM_CONCAT(COURSE_NAME)
5FROM   T_ROW_COL_LHR
6GROUP  BY NUM,NAME,
7          SEX,
8          CLASSES;

结果如下所示：

 
3、多列转换成字符串
使用||或CONCAT函数实现，示例代码如下所示：
1SELECT CONCAT('A','B') FROM DUAL;



4、多行转换成字符串
示例代码如下所示：
 1CREATE TABLE T_ROW_STR( 
 2ID INT, 
 3COL VARCHAR2(10)
 4); 
 5INSERT INTO T_ROW_STR VALUES(1,'A'); 
 6INSERT INTO T_ROW_STR VALUES(1,'B'); 
 7INSERT INTO T_ROW_STR VALUES(1,'C'); 
 8INSERT INTO T_ROW_STR VALUES(2,'A'); 
 9INSERT INTO T_ROW_STR VALUES(2,'D'); 
10INSERT INTO T_ROW_STR VALUES(2,'E'); 
11INSERT INTO T_ROW_STR VALUES(3,'C'); 
12COMMIT; 
13SELECT * FROM T_ROW_STR; 

查询结果如下所示：
 下面进行转换：
 1SELECT ID,
 2       MAX(DECODE(RN, 1, COL, NULL)) ||
 3       MAX(DECODE(RN, 2, ',' || COL, NULL)) ||
 4       MAX(DECODE(RN, 3, ',' || COL, NULL)) STR
 5FROM   (SELECT ID,
 6               COL,
 7               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN
 8        FROM   T_ROW_STR) T
 9GROUP  BY ID
10ORDER  BY 1;

 
也可以使用SYS_CONNECT_BY_PATH来实现：
 1SELECT T.ID ID,
 2       MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.COL, ','), 2)) STR
 3FROM   (SELECT ID,
 4               COL,
 5               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) RN
 6        FROM   T_ROW_STR) T
 7START  WITH RN = 1
 8CONNECT BY RN = PRIOR RN + 1
 9    AND    ID = PRIOR ID
10GROUP  BY T.ID;

也可以使用LISTAGG函数直接转换：
1SELECT t.id,
2       LISTAGG(t.col,',') WITHIN GROUP (ORDER BY t.id desc )
3FROM   T_ROW_STR t
4GROUP  BY t.id;

也可以使用VM_CONCAT函数直接转换：
1SELECT t.id,
2       WM_CONCAT(t.col)
3FROM   T_ROW_STR t
4GROUP  BY t.id;

查询结果如下所示：
 
5、字符串转换成多列
实际上就是一个字符串拆分的问题。示例代码如下所示：
 1CREATE TABLE T_COL_ROW( 
 2ID INT, 
 3C1 VARCHAR2(10), 
 4C2 VARCHAR2(10), 
 5C3 VARCHAR2(10)); 
 6
 7INSERT INTO T_COL_ROW VALUES (1, 'v11', 'v21', 'v31'); 
 8INSERT INTO T_COL_ROW VALUES (2, 'v12', 'v22', NULL); 
 9INSERT INTO T_COL_ROW VALUES (3, 'v13', NULL, 'v33'); 
10INSERT INTO T_COL_ROW VALUES (4, NULL, 'v24', 'v34'); 
11INSERT INTO T_COL_ROW VALUES (5, 'v15', NULL, NULL); 
12INSERT INTO T_COL_ROW VALUES (6, NULL, NULL, 'v35'); 
13INSERT INTO T_COL_ROW VALUES (7, NULL, NULL, NULL); 
14COMMIT; 
15
16SELECT * FROM T_COL_ROW; 
17
18CREATE TABLE T_STR_COL AS 
19SELECT ID,C1||','||C2||','||C3 AS C123 
20FROM T_COL_ROW; 
21
22SELECT * FROM T_STR_COL; 

查询结果如下所示：
下面进行转换：
 1SELECT ID,
 2       C123,
 3       SUBSTR(C123, 1, INSTR(C123 || ',', ',', 1, 1) - 1) C1,
 4       SUBSTR(C123,
 5              INSTR(C123 || ',', ',', 1, 1) + 1,
 6              INSTR(C123 || ',', ',', 1, 2) - INSTR(C123 || ',', ',', 1, 1) - 1) C2,
 7       SUBSTR(C123,
 8              INSTR(C123 || ',', ',', 1, 2) + 1,
 9              INSTR(C123 || ',', ',', 1, 3) - INSTR(C123 || ',', ',', 1, 2) - 1) C3
10FROM   T_STR_COL
11ORDER  BY 1;

查询结果如下所示：

 
6、字符串转换成多行
示例代码如下所示：
 1CREATE TABLE T_STR_ROW AS 
 2SELECT ID, 
 3       MAX(DECODE(RN, 1, COL, NULL)) || 
 4              MAX(DECODE(RN, 2, ',' || COL, NULL)) || 
 5              MAX(DECODE(RN, 3, ',' || COL, NULL)) STR 
 6  FROM (SELECT ID, 
 7               COL, 
 8               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY COL) AS RN 
 9          FROM T_ROW_STR) T 
10  GROUP BY ID
11  ORDER BY 1;
12SELECT * FROM T_STR_ROW; 

查询结果如下所示：

转换代码如下：
 1SELECT ID,
 2       1 AS P,
 3       SUBSTR(STR, 1, INSTR(STR || ',', ',', 1, 1) - 1) AS CV
 4FROM   T_STR_ROW
 5UNION ALL
 6SELECT ID,
 7       2 AS P,
 8       SUBSTR(STR,
 9              INSTR(STR || ',', ',', 1, 1) + 1,
10              INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV
11FROM   T_STR_ROW
12UNION ALL
13SELECT ID,
14       3 AS P,
15       SUBSTR(STR,
16              INSTR(STR || ',', ',', 1, 1) + 1,
17              INSTR(STR || ',', ',', 1, 2) - INSTR(STR || ',', ',', 1, 1) - 1) AS CV
18FROM   T_STR_ROW
19ORDER  BY 1,
20          2;

查询结果如下所示：

还有几类特殊的转换，如下所示：
1CREATE OR REPLACE TYPE INS_SEQ_TYPE IS VARRAY(8) OF NUMBER; 
2SELECT * FROM TABLE(INS_SEQ_TYPE(1, 2, 3, 4, 5));

结果：
1COLUMN_VALUE
2------------
3           1
4           2
5           3
6           4
7           5


若是字符串类型，则如下所示：
1CREATE OR REPLACE TYPE INS_SEQ_TYPE2 IS VARRAY(80) OF VARCHAR2(32767);
2SELECT * FROM TABLE(INS_SEQ_TYPE2('aadf,dea','cbc','d'));


结果：
1COLUMN_VALUE
2-----------------
3aadf,dea
4cbc
5d


还有如下的形式：
先创建一个TYPE类型，代码如下：
1CREATE OR REPLACE TYPE TYPE_STR_LHR IS TABLE OF VARCHAR2(32767);

再创建FUN_SPLIT2_LHR函数，代码如下：
 1CREATE OR REPLACE FUNCTION FUN_SPLIT2_LHR(P_STR       VARCHAR2,
 2                                 V_SPLIT VARCHAR2 DEFAULT ',') RETURN TYPE_STR_LHR IS
 3  RS    TYPE_STR_LHR := TYPE_STR_LHR();
 4  V_STR VARCHAR2(4000) := '';
 5  V_LEN NUMBER := 0;
 6BEGIN
 7  V_STR := P_STR;
 8  V_LEN := LENGTH(V_SPLIT);
 9  WHILE LENGTH(V_STR) > 0 LOOP
10    IF INSTR(V_STR, V_SPLIT) > 0 THEN
11      RS.EXTEND;
12      RS(RS.COUNT) := SUBSTR(V_STR, 1, INSTR(V_STR, V_SPLIT) - 1);
13      V_STR := SUBSTR(V_STR, INSTR(V_STR, V_SPLIT) + V_LEN);
14    ELSE
15      RS.EXTEND;
16      RS(RS.COUNT) := V_STR;
17      EXIT;
18    END IF;
19  END LOOP;
20  RETURN RS;
21
22END;


测试如下：
 1SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101,102,103',','));
 2
 3COLUMN_VALUE
 4------------------
 5101
 6102
 7103
 8SQL> SELECT TO_NUMBER(COLUMN_VALUE) FROM TABLE(FUN_SPLIT2_LHR('101,102,103'));
 9
10TO_NUMBER(COLUMN_VALUE)
11-----------------------
12                   101
13                   102
14                   103
15SQL> SELECT COLUMN_VALUE FROM TABLE(FUN_SPLIT2_LHR('101@#102@#103','@#')); 
16COLUMN_VALUE
17---------------
18101
19102
20103


& 说明：
有关行列互换更多的案例可以参考我的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：
 1CREATE TABLE SALES(年 NUMBER,季度 NUMBER,销售量 NUMBER);
 2INSERT INTO SALES VALUES(1991, 1 ,11);
 3INSERT INTO SALES VALUES(1991, 2 ,12);
 4INSERT INTO SALES VALUES(1991, 3 ,13);
 5INSERT INTO SALES VALUES(1991, 4 ,14);
 6INSERT INTO SALES VALUES(1992, 1 ,21);
 7INSERT INTO SALES VALUES(1992, 2 ,22);
 8INSERT INTO SALES VALUES(1992, 3 ,23);
 9INSERT INTO SALES VALUES(1992, 4 ,24);
10SELECT * FROM SALES;

此题若使用聚合函数+DECODE或CASE来回答，如下所示：
1SELECT 年, 
2    SUM(CASE WHEN 季度=1 THEN 销售量 ELSE 0 END) AS 一季度,
3    SUM(CASE WHEN 季度=2 THEN 销售量 ELSE 0 END) AS 二季度,
4    SUM(CASE WHEN 季度=3 THEN 销售量 ELSE 0 END) AS 三季度,
5    SUM(CASE WHEN 季度=4 THEN 销售量 ELSE 0 END) AS 四季度
6FROM SALES 
7GROUP BY 年
8ORDER BY T.教师号;

此题若使用PIVOT函数，如下所示：
1SELECT *
2  FROM SALES
3PIVOT(SUM(销售量)
4   FOR 季度 IN(1 AS "一季度", 2 AS "二季度", 3 AS "三季度", 4 AS "四季度"))
5 ORDER BY 1;

此题若使用临时表的方式，如下所示：
 1SELECT T.年,
 2       NVL(SUM(T1.一季度),0) AS "一季度",
 3       NVL(SUM(T2.二季度),0) AS "二季度",
 4       NVL(SUM(T3.三季度),0) AS "三季度",
 5       NVL(SUM(T4.四季度),0) AS "四季度"
 6  FROM (SELECT 年,销售量 AS "一季度" FROM SALES A WHERE A.季度 = '1') T1,
 7       (SELECT 年,销售量 AS "二季度" FROM SALES A WHERE A.季度 = '2') T2, 
 8       (SELECT 年,销售量 AS "三季度" FROM SALES A WHERE A.季度 = '3') T3, 
 9       (SELECT 年,销售量 AS "四季度" FROM SALES A WHERE A.季度 = '4') T4,
10       (SELECT DISTINCT 年 FROM SALES) T
11 WHERE T.年 = T1.年(+)
12   AND T.年 = T2.年(+)
13   AND T.年 = T3.年(+)
14   AND T.年 = T4.年(+)
15 GROUP BY T.年
16 ORDER BY 1; 


真题2、有如下的表格：
 1create table test(
 2   id number(10) primary key,
 3   type number(10) ,
 4   t_id number(10),
 5   value varchar2(6)
 6);
 7
 8insert into test values(100,1,1,'张三');
 9insert into test values(200,2,1,'男');
10insert into test values(300,3,1,'50');
11
12insert into test values(101,1,2,'刘二');
13insert into test values(201,2,2,'男');
14insert into test values(301,3,2,'30');
15
16insert into test values(102,1,3,'刘三');
17insert into test values(202,2,3,'女');
18insert into test values(302,3,3,'10');
19
20select * from test; 

查询结果如下所示：
根据以上代码生成的表写出一条查询语句，查询结果如下：
1姓名 性别 年龄 
2张三  男  50 
3刘二  男  30 
4刘三  女  10 


答案：根据表格可以分析出TYPE列中1代表姓名、2代表性别、3代表年龄，而T_ID中ID一样的为同一个人的属性。查询结果中列依次为姓名、性别、年龄，而TYPE列决定姓名、性别、年龄。
方法一：使用分组，先对T_ID进行分组，然后用DECODE函数过滤数据：
1SELECT MAX(decode(TYPE, 1, VALUE)) "姓名",
2       MAX(decode(TYPE, 2, VALUE)) "性别",
3       MAX(decode(TYPE, 3, VALUE)) "年龄"
4FROM   test
5GROUP  BY t_id;

方法二：使用连表，通过WHERE过滤生成3张TYPE分别等于1（姓名）、2（性别）、3（年龄）的3张虚拟表，再通过WHERE连接条件三张表T_ID相等的为同一个人或者说同一条记录（行）：
 1SELECT t1.value "姓名",
 2       t2.value "性别",
 3       t3.value "年龄"
 4FROM   (SELECT VALUE,
 5               t_id
 6        FROM   test
 7        WHERE  TYPE = 1) t1,
 8       (SELECT VALUE,
 9               t_id
10        FROM   test
11        WHERE  TYPE = 2) t2,
12       (SELECT VALUE,
13               t_id
14        FROM   test
15        WHERE  TYPE = 3) t3
16WHERE  t1.t_id = t2.t_id
17AND    t1.t_id = t3.t_id;

结果如下所示：
 

真题3、有如下表内容：
12005-05-09 胜
22005-05-09 胜
32005-05-09 负
42005-05-09 负
52005-05-10 胜
62005-05-10 负
72005-05-10 负

如果要生成下列结果，那么该如何编写SQL语句？
1            胜 负
22005-05-09  2  2
32005-05-10  1  2

其中，建表语句如下所示：
1create table tmp(rq varchar2(10),shengfu varchar2(5));
2insert into tmp values('2005-05-09','胜');
3insert into tmp values('2005-05-09','胜');
4insert into tmp values('2005-05-09','负');
5insert into tmp values('2005-05-09','负');
6insert into tmp values('2005-05-10','胜');
7insert into tmp values('2005-05-10','负');
8insert into tmp values('2005-05-10','负');
9select * from tmp;


答案：方法一：使用分组。按日期分组，用conut函数计算次数。
1SELECT rq "日期",
2       COUNT(decode(shengfu, '胜', 1)) "胜",
3       COUNT(decode(shengfu, '负', 1)) "负"
4FROM   tmp
5GROUP  BY rq
6ORDER  BY rq;


方法二：使用连表。
 1SELECT t1.rq,
 2       t1.胜,
 3       t2.负
 4FROM   (SELECT COUNT(decode(shengfu, '胜', 1)) "胜",
 5               rq
 6        FROM   tmp
 7        GROUP  BY rq) t1
 8JOIN   (SELECT COUNT(decode(shengfu, '负', 1)) "负",
 9               rq
10        FROM   tmp
11        GROUP  BY rq) t2
12ON     t1.rq = t2.rq;


真题4、有如下的表：
 1create table STUDENT_SCORE
 2(
 3  name    VARCHAR2(20),
 4  subject VARCHAR2(20),
 5  score   NUMBER(4,1)
 6);
 7insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
 8insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
 9insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
10insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
11insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
12insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
13insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
14insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
15insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);
16select * from STUDENT_SCORE;

表STUDENT_SCORE中数据：
如要得到类似下面的结果，那么该如何编写SQL语句：
答案：方法一：使用分组
 1SELECT NAME "姓名",
 2       MAX(decode(subject, '语文' ，score)) "语文",
 3       MAX(decode(subject, '数学' ，score)) "数学",
 4       MAX(decode(subject, '英语' ，score)) "英语"
 5FROM   STUDENT_SCORE
 6GROUP  BY NAME;
 7方法二：使用连表
 8SELECT t1.name  姓名,
 9       t1.score 语文,
10       t2.score 数学,
11       t3.score 英语
12FROM   (SELECT NAME,
13               score
14        FROM   STUDENT_SCORE
15        WHERE  subject = '语文') t1
16JOIN   (SELECT NAME,
17               score
18        FROM   STUDENT_SCORE
19        WHERE  subject = '数学') t2
20ON     t1.name = t2.name
21JOIN   (SELECT NAME,
22               score
23        FROM   STUDENT_SCORE
24        WHERE  subject = '英语') t3
25ON     t1.name = t3.name;

如果大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格，那么请继续给出SQL语句：
 1SELECT t.姓名,
 2       (CASE
 3           WHEN t.语文 >= 80 THEN
 4            '优秀'
 5           WHEN t.语文 >= 60 THEN
 6            '及格'
 7           ELSE
 8            '不及格'
 9       END) 语文,
10       (CASE
11           WHEN t.数学 >= 80 THEN
12            '优秀'
13           WHEN t.数学 >= 60 THEN
14            '及格'
15           ELSE
16            '不及格'
17       END) 数学,
18       (CASE
19           WHEN t.英语 >= 80 THEN
20            '优秀'
21           WHEN t.英语 >= 60 THEN
22            '及格'
23           ELSE
24            '不及格'
25       END) 英语
26FROM   (SELECT t1.name  姓名,
27               t1.score 语文,
28               t2.score 数学,
29               t3.score 英语
30        FROM   (SELECT NAME,
31                       score
32                FROM   STUDENT_SCORE
33                WHERE  subject = '语文') t1
34        JOIN   (SELECT NAME,
35                      score
36               FROM   STUDENT_SCORE
37               WHERE  subject = '数学') t2
38        ON     t1.name = t2.name
39        JOIN   (SELECT NAME,
40                      score
41               FROM   STUDENT_SCORE
42               WHERE  subject = '英语') t3
43        ON     t1.name = t3.name) t;


结果：

真题5、有如下2张表：
yj01表：
1月份mon 部门dep 业绩yj
2-----------------------
3一月份      1      10
4一月份      2      10
5一月份      3      5
6二月份      2      8
7二月份      4      9
8三月份      3      8

yjdept表：
1部门dep      部门名称dname
2--------------------
3 1    国内业务一部
4 2    国内业务二部
5 3    国内业务三部
6 4    国际业务部

建表语句分别如下所示：
 1create table yj01(
 2       month varchar2(10),
 3       deptno number(10),
 4       yj number(10)
 5);
 6
 7insert into yj01(month,deptno,yj) values('一月份',1,10);
 8insert into yj01(month,deptno,yj) values('二月份',2,10);
 9insert into yj01(month,deptno,yj) values('二月份',3,5);
10insert into yj01(month,deptno,yj) values('三月份',2,8);
11insert into yj01(month,deptno,yj) values('三月份',4,9);
12insert into yj01(month,deptno,yj) values('三月份',3,8);
13
14create table yjdept(
15       deptno number(10),
16       dname varchar2(20)
17);
18
19insert into yjdept(deptno,dname) values(1,'国内业务一部');
20insert into yjdept(deptno,dname) values(2,'国内业务二部');
21insert into yjdept(deptno,dname) values(3,'国内业务三部');
22insert into yjdept(deptno,dname) values(4,'国际业务部');
23select * from yj01;
24select * from yjdept;

请按照月份和部门进行分组计算。
答案：使用分组：
1SELECT deptno,
2       MAX(decode(MONTH, '一月份', yj)) 一月份,
3       MAX(decode(MONTH, '二月份', yj)) 二月份,
4       MAX(decode(MONTH, '三月份', yj)) 三月份
5FROM   yj01
6GROUP  BY deptno
7ORDER  BY deptno;

使用连接：
 1SELECT t1.deptno,
 2       t1.yj     一月份,
 3       t2.yj     二月份,
 4       t3.yj     三月份
 5FROM   (SELECT y2.deptno,
 6               y1.yj
 7        FROM   (SELECT yj,
 8                       deptno
 9                FROM   yj01
10                WHERE  MONTH = '一月份') y1
11        RIGHT  JOIN yjdept y2
12        ON     y1.deptno = y2.deptno) t1
13JOIN   (SELECT y2.deptno,
14               y1.yj
15        FROM   (SELECT yj,
16                       deptno
17                FROM   yj01
18                WHERE  MONTH = '二月份') y1
19        RIGHT  JOIN yjdept y2
20        ON     y1.deptno = y2.deptno) t2
21ON     t1.deptno = t2.deptno
22JOIN   (SELECT y2.deptno,
23               y1.yj
24        FROM   (SELECT yj,
25                       deptno
26                FROM   yj01
27                WHERE  MONTH = '三月份') y1
28        RIGHT  JOIN yjdept y2
29        ON     y1.deptno = y2.deptno) t3
30ON     t1.deptno = t3.deptno
31ORDER  BY t1.deptno;

结果：

真题6、有表T1，其数据如下所示：
1STUDENT  SUBJECT GRADE
2---------------------------
3student1  语文  80
4student1  数学  70
5student1  英语  60
6student2  语文  90
7student2  数学  80
8student2  英语  100

现需要转换为如下的形式：
1student   语文   数学   英语
2student1   80    70     60
3student2   90    80     100

试写出其SQL语句。
答案：该题属于固定列数的行转列，环境如下：
1DROP TABLE T1;
2CREATE TABLE T1(STUDENT VARCHAR2(30),SUBJECT VARCHAR2(30)  ,GRADE NUMBER);
3INSERT INTO T1 VALUES('student1', '语文', 80 );
4INSERT INTO T1 VALUES('student1', '数学', 70 );
5INSERT INTO T1 VALUES('student1', '英语', 60 );
6INSERT INTO T1 VALUES('student2', '语文', 90 );
7INSERT INTO T1 VALUES('student2', '数学', 80 );
8INSERT INTO T1 VALUES('student2', '英语', 100);
9SELECT * FROM t1;

若采用MAX或CASE+DECODE或CASE来回答，如下所示：
1SELECT STUDENT,
2       SUM(DECODE(SUBJECT, '语文', GRADE, NULL)) "语文",
3       SUM(DECODE(SUBJECT, '数学', GRADE, NULL)) "数学",
4       SUM(DECODE(SUBJECT, '英语', GRADE, NULL)) "英语"
5  FROM T1
6 GROUP BY STUDENT;

此题若使用PIVOT函数，如下所示：
1SELECT *
2  FROM T1
3PIVOT(SUM(GRADE)
4   FOR SUBJECT IN('语文' AS "语文", '数学' AS "数学", '英语' AS "英语"))
5 ORDER BY 1;

此题若使用临时表的方式，如下所示：
 1SELECT T.STUDENT,
 2       NVL(SUM(T1.语文),0) AS "语文",
 3       NVL(SUM(T2.数学),0) AS "数学",
 4       NVL(SUM(T3.英语),0) AS "英语" 
 5  FROM (SELECT STUDENT,GRADE AS "语文" FROM T1 A WHERE A.SUBJECT = '语文') T1,
 6       (SELECT STUDENT,GRADE AS "数学" FROM T1 A WHERE A.SUBJECT = '数学') T2, 
 7       (SELECT STUDENT,GRADE AS "英语" FROM T1 A WHERE A.SUBJECT = '英语') T3, 
 8       (SELECT DISTINCT STUDENT FROM T1) T
 9 WHERE T.STUDENT = T1.STUDENT(+)
10   AND T.STUDENT = T2.STUDENT(+)
11   AND T.STUDENT = T3.STUDENT(+) 
12 GROUP BY T.STUDENT
13 ORDER BY 1; 


真题7、有表T1结构如下：
1c1     c2
2---- ---------
31      我
41      是
51      谁
62      知
72      道
83      不
9……

现需要转换为如下形式：
11   我是谁
22   知道
33   不

试写出其SQL语句。
答案：此题可以使用listagg函数或者定制的WM_CONCAT函数（参考：【DB笔试面试459】ORA-00904: "wm_concat":invalid identifier错误如何解决？）来实现。listagg函数如下所示：
 1WITH TEMP AS(
 2  SELECT '1' c1 ,'我' c2 FROM DUAL UNION ALL
 3  SELECT '1' c1 ,'是' c2 FROM DUAL UNION ALL
 4  SELECT '1' c1 ,'谁' c2 FROM DUAL UNION ALL
 5  SELECT '2' c1 ,'知' c2 FROM DUAL UNION ALL
 6  SELECT '2' c1 ,'道' c2 FROM DUAL UNION ALL
 7  SELECT '3' c1 ,'不' c2 FROM DUAL
 8)
 9SELECT c1,LISTAGG(c2,'') WITHIN GROUP (ORDER BY c2 desc )
10FROM TEMP
11GROUP BY c1;

另外，也可以借助于PL/SQL来完成，这里给一个简单的例子，其中默认原表名为T：
 1CREATE OR REPLACE FUNCTION GET_C2(TMP_C1 NUMBER) RETURN VARCHAR2 IS
 2  COL_C2 VARCHAR2(4000);
 3BEGIN
 4  FOR CUR IN (SELECT C2 FROM T WHERE C1 = TMP_C1) LOOP
 5    COL_C2 := COL_C2 || CUR.C2;
 6  END LOOP;
 7  COL_C2 := RTRIM(COL_C2, 1);
 8  RETURN COL_C2;
 9END;
10/
11SQL> SELECT DISTINCT C1 ,GET_C2(C1) CC2 FROM TABLE;


& 说明：
有关行列互换更多的案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1272538/


本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗。