【DB笔试面试467】Oracle中行列互换有哪些方法？

小麦苗DBA宝典

发布于 2019-09-29 15:40:00

1.6K0

发布于 2019-09-29 15:40:00

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

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程序员面试笔试宝典》，作者：小麦苗。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-05-17，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

sql

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

编程算法

sql

【DB笔试面试467】Oracle中行列互换有哪些方法？

【DB笔试面试467】Oracle中行列互换有哪些方法？

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐