zhu的SQL行转列自述（分别用Oracle和Mysql实现）

最新推荐文章于 2023-04-20 17:48:28 发布

iamzhuwenhui

最新推荐文章于 2023-04-20 17:48:28 发布

阅读量719

点赞数

本文链接：https://blog.csdn.net/u013632854/article/details/77143802

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

有时候存进去的数据与你要查询并展示的数据是不对应的，某个场景下就需要转换的，这是个苦逼活

有时候面试就会问这个苦逼问题

有时候你就是闲的蛋疼，就会折腾这个东西

那么问题来了，行转列的关键在于：

1、找到分组关键
- 需求一般是这样：找到每个人的每科成绩并展示，找到每个月的数据并展示等
  你看，都要分组，每个人是一组，每个月也是一组，所以关键在于找到分组的字段
  分组的作用在于将相关联的数据整合到一行
2、妙用分组函数
- 使用了分组，在select后面就不能随便加内容了，必须跟分组函数，这是语法规定的
  只需要单纯展示内容，可以使用max()函数包裹内容，因为你max之后也是你自己，这个是重点
  如果是算总数，可以使用sum
  如果是统计次数，可以使用count
3、必备decode或者if函数匹配数据
- 以上面截图为例，你以T_ID分组了，但是数据怎么取，VALUE里面包含了我要展示的所有内容
  这个时候需要用到decode，就像java中的if语句一样，if(value==”张三”)的时候做相应的操作
  以上面截图为例，很容易看出：TYPE=1为姓名、TYPE=2为性别，TYPE=3为年龄
  select * from test1;
  select max(decode(TYPE, 1, VALUE)) “姓名”,
  max(decode(TYPE, 2, VALUE)) “性别”,
  max(decode(TYPE, 3, VALUE)) “年龄”
  from test1
  group by T_ID
  为什么是直接用value呢？其实过程是这样的：
  匹配到第一条数据：type是1，value是张三
  max(decode(TYPE, 1, VALUE)) “姓名”, 输出张三
  max(decode(TYPE, 2, VALUE)) “性别”, 输出 null
  max(decode(TYPE, 3, VALUE)) “年龄” 输出 null
  匹配到第二条数据：type是2，value是男
  max(decode(TYPE, 1, VALUE)) “姓名”, 输出 null
  max(decode(TYPE, 2, VALUE)) “性别”, 输出男
  max(decode(TYPE, 3, VALUE)) “年龄” 输出 null
  然后就变下面这样了
窝草，我要的是这样的吧！莫急，这个时候要max出马
通过max函数，张三，男，50就会挤到一行去了

说多了都是泪，上题：

（题目来自全能的百度，答案自己折腾的，分别用Oracle和Mysql实现）

一、使用Oracle

/*
------------------------------------------经典面试题--1
create table test1(
   id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(10)
);

insert into test1 values(100,1,1,'张三');
insert into test1 values(200,2,1,'男');
insert into test1 values(300,3,1,'50');

insert into test1 values(101,1,2,'刘二');
insert into test1 values(201,2,2,'男');
insert into test1 values(301,3,2,'30');

insert into test1 values(102,1,3,'刘三');
insert into test1 values(202,2,3,'女');
insert into test1 values(302,3,3,'10');

请写出一条查询语句结果如下：
姓名      性别     年龄
--------- -------- ----
张三       男        50
刘二       男        30
六三       女        10
*/
--总数据，验证用
select * from test1;
--方法一：
select result1.value "姓名", result2.value "性别", result3.value "年龄"
  from (select t1.t_id, t1.value from test1 t1 where t1.type = 1) result1
  join (select t1.t_id, t1.value from test1 t1 where t1.type = 2) result2
    on result1.t_id = result2.t_id
  join (select t1.t_id, t1.value from test1 t1 where t1.type = 3) result3
    on result3.t_id = result2.t_id;
--方法二：
select max(decode(TYPE, 1, VALUE)) "姓名",
       max(decode(TYPE, 2, VALUE)) "性别",
       max(decode(TYPE, 3, VALUE)) "年龄"
  from test1
group by T_ID
/*
------------------------------------------经典面试题--2
create table test2(rq varchar2(10),sf varchar2(5));
insert into test2 values('2005-05-09','胜');
insert into test2 values('2005-05-09','胜');
insert into test2 values('2005-05-09','负');
insert into test2 values('2005-05-09','负');
insert into test2 values('2005-05-10','胜');
insert into test2 values('2005-05-10','负');
insert into test2 values('2005-05-10','负');
------------------------------------------
使用group by，表内容如下：
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
*/
--总数据
select * from test2;
--展示
select t2.rq "日期",
       count(decode(t2.sf, '胜', 1)) "胜"，count(decode(t2.sf, '负', 1)) "负"
  from test2 t2
group by t2.rq
order by t2.rq

/*
------------------------------------------经典面试题--3

create table test3(A varchar2(5),B varchar2(5),C varchar2(5));
insert into test3 values(15,16,17);

来一发查询问题如下，表中有A B C三列,用SQL语句实现：
当A列大于B列时选择A列否则选择B列，
当B列大于C列时选择B列否则选择C列。
*/
--总数据
select * from test3;
--结果：
select case
         when t3.a > t3.b then
          "A"
         else
          (case
            when t3.b > t3.c then
             "B"
            else
             "C"
          end)
       end "num is"
  from test3 t3;
/*
------------------------------------------经典面试题--4
create table test4
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);
insert into test4 (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into test4 (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

问题1、请编写sql语句得到类似下面的结果
姓名   语文   数学   英语
王五    99     66    91

问题2、有一张表，里面有3个字段：语文，数学，英语。
请用一条sql语句查询出这三条记录并按以下条件显示出来：  
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
显示格式：  
姓名   语文              数学                英语  
王五   及格             优秀                不及格 
*/
--总数据
select * from test4;
--题1：
select t4.name "名字",
       max(decode(t4.subject, '语文', t4.score)) "语文",
       max(decode(t4.subject, '数学', t4.score)) "数学",
       max(decode(t4.subject, '英语', t4.score)) "英语"
  from test4 t4
group by t4.name;
--题2：
--方法一：
select t4.name "名字",
       max(decode(t4.subject, '语文', (case
         when t4.score >= 80 then
          '优秀'
         when t4.score >= 60 and t4.score < 80 then
          '及格'
         when t4.score < 60 then
          '不及格'
       end))) "语文",
       max(decode(t4.subject, '数学', (case
         when t4.score >= 80 then
          '优秀'
         when t4.score >= 60 and t4.score < 80 then
          '及格'
         when t4.score < 60 then
          '不及格'
       end))) "数学",
       max(decode(t4.subject, '英语', (case
         when t4.score >= 80 then
          '优秀'
         when t4.score >= 60 and t4.score < 80 then
          '及格'
         when t4.score < 60 then
          '不及格'
       end))) "英语"
  from test4 t4
group by t4.name;
--方法二：
select cj.name,
       case
         when cj.yw >= 80 then
          '优秀'
         when cj.yw >= 60 and cj.yw < 80 then
          '及格'
         when cj.yw < 60 then
          '不及格'
       end "语文",
       case
         when cj.sx >= 80 then
          '优秀'
         when cj.sx >= 60 and cj.sx < 80 then
          '及格'
         when cj.sx < 60 then
          '不及格'
       end "数学",
       case
         when cj.yy >= 80 then
          '优秀'
         when cj.yy >= 60 and cj.yy < 80 then
          '及格'
         when cj.yy < 60 then
          '不及格'
       end "英语"
  from (select t4.name name,
               max(decode(t4.subject, '语文', t4.score)) yw,
               max(decode(t4.subject, '数学', t4.score)) sx,
               max(decode(t4.subject, '英语', t4.score)) yy
          from test4 t4
         group by t4.name) cj;

/*
------------------------------------------经典面试题--5
create table test5_yj(
       month varchar2(10),
       deptno number(10),
       yj number(10)
);

insert into test5_yj(month,deptno,yj) values('一月份',01,10);
insert into test5_yj(month,deptno,yj) values('二月份',02,10);
insert into test5_yj(month,deptno,yj) values('二月份',03,5);
insert into test5_yj(month,deptno,yj) values('三月份',02,8);
insert into test5_yj(month,deptno,yj) values('三月份',04,9);
insert into test5_yj(month,deptno,yj) values('三月份',03,8);

create table test5_dept(
       deptno number(10),
       dname varchar2(20)
);

insert into test5_dept(deptno,dname) values(01,'国内业务一部');
insert into test5_dept(deptno,dname) values(02,'国内业务二部');
insert into test5_dept(deptno,dname) values(03,'国内业务三部');
insert into test5_dept(deptno,dname) values(04,'国际业务部');

test5_yj展示如下：
月份mon 部门dep 业绩yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8

test5_dept展示如下：
部门dep      部门名称dname
--------------------------------
      01      国内业务一部
      02      国内业务二部
      03      国内业务三部
      04      国际业务部

使用一个sql语将两个表中的数据按如下格式输出
注意以下提供的数据及结果不准确，仅作为格式参考

输出：
     部门dep     一月份     二月份    三月份
------------------------------------------
国内业务一部      10         0        0
国内业务二部      10         8        0
国内业务三部      0          5        8
国际业务部        0          0        9
------------------------------------------

*/
--总数据
select * from test5_yj;
select * from test5_dept;

--展示数据
--方法一
select d.dname,
       nvl(result.one, 0) "一月份",
       nvl(result.two, 0) "二月份 ",
       nvl(result.three, 0) "三月份 "
  from (select t.deptno， sum(decode(t.month, '一月份' ，t.yj)) one， sum(decode(t.month, '二月份' ，t.yj)) two， sum(decode(t.month, '三月份' ，t.yj)) three
          from test5_yj t
         group by t.deptno) result,
       test5_dept d
where result.deptno = d.deptno
order by d.dname desc;

--方法二
select test5_dept.dname "部门",
       result_yj.yi    "一月份",
       result_yj.e     "二月份",
       result_yj.san   "三月份"
  from (select deptno,
               sum(decode(month, '一月份', yj, 0)) yi,
               sum(decode(month, '二月份', yj, 0)) e,
               sum(decode(month, '三月份', yj, 0)) san
          from test5_yj
         group by deptno) result_yj,
       test5_dept
where result_yj.deptno = test5_dept.deptno

二、使用Mysql

 ---------------------------------------------------题1

CREATE TABLE test1(
   id INT(10) PRIMARY KEY,
   TYPE INT(10) ,
   t_id INT(10),
   VALUE VARCHAR(10)
);

INSERT INTO test1 VALUES(100,1,1,'张三');
INSERT INTO test1 VALUES(200,2,1,'男');
INSERT INTO test1 VALUES(300,3,1,'50');

INSERT INTO test1 VALUES(101,1,2,'刘二');
INSERT INTO test1 VALUES(201,2,2,'男');
INSERT INTO test1 VALUES(301,3,2,'30');

INSERT INTO test1 VALUES(102,1,3,'刘三');
INSERT INTO test1 VALUES(202,2,3,'女');
INSERT INTO test1 VALUES(302,3,3,'10');

SELECT * FROM test1;

SELECT MAX(IF(TYPE=1, VALUE,"")) "姓名",
       MAX(IF(TYPE=2, VALUE,"")) "性别",
       MAX(IF(TYPE=3, VALUE,"")) "年龄"
  FROM test1
GROUP BY T_ID

---------------------------------------------------题2

CREATE TABLE test2(rq VARCHAR(10),sf VARCHAR(5));
INSERT INTO test2 VALUES('2005-05-09','胜');
INSERT INTO test2 VALUES('2005-05-09','胜');
INSERT INTO test2 VALUES('2005-05-09','负');
INSERT INTO test2 VALUES('2005-05-09','负');
INSERT INTO test2 VALUES('2005-05-10','胜');
INSERT INTO test2 VALUES('2005-05-10','负');
INSERT INTO test2 VALUES('2005-05-10','负');

SELECT * FROM test2;

SELECT t2.rq "日期",
       SUM(IF(t2.sf='胜', 1,0)) "胜",SUM(IF(t2.sf='负', 1,0)) "负"
  FROM test2 t2
GROUP BY t2.rq
ORDER BY t2.rq

---------------------------------------------------题3

CREATE TABLE test3(A VARCHAR(5),B VARCHAR(5),C VARCHAR(5));
INSERT INTO test3 VALUES(15,16,17);

SELECT * FROM test3;

SELECT CASE
         WHEN t3.a > t3.b THEN
          t3.a
         ELSE
          (CASE
            WHEN t3.b > t3.c THEN
             t3.b
            ELSE
             t3.c
          END)
       END "num is"
  FROM test3 t3;

---------------------------------------------------题4

CREATE TABLE test4
(
  NAME    VARCHAR(20),
  SUBJECT VARCHAR(20),
  score   DOUBLE(4,1)
);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 78.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 88.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('张三', '英语', 98.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('李四', '语文', 89.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('李四', '数学', 76.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('李四', '英语', 90.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('王五', '语文', 99.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('王五', '数学', 66.0);
INSERT INTO test4 (NAME, SUBJECT, SCORE) VALUES ('王五', '英语', 91.0);

-- 总数据
SELECT * FROM test4;

-- 题1：
SELECT t4.name "名字",
       MAX(IF(t4.subject='语文', t4.score,0)) "语文",
       MAX(IF(t4.subject='数学', t4.score,0)) "数学",
       MAX(IF(t4.subject='英语', t4.score,0)) "英语"
  FROM test4 t4
GROUP BY t4.name;
-- 题2：
SELECT t4.name "名字",
       MIN(IF(t4.subject='语文', (CASE
         WHEN t4.score >= 80.0 THEN
          '优秀'
         WHEN t4.score >= 60.0 AND t4.score < 80 THEN
          '及格'
         WHEN t4.score < 60.0 THEN
          '不及格'
       END),'没有成绩')) "语文",
       MIN(IF(t4.subject='数学', (CASE
         WHEN t4.score >= 80 THEN
          '优秀'
         WHEN t4.score >= 60 AND t4.score < 80 THEN
          '及格'
         WHEN t4.score < 60 THEN
          '不及格'
       END),'没有成绩')) "数学",
       MIN(IF(t4.subject='英语', (CASE
         WHEN t4.score >= 80 THEN
          '优秀'
         WHEN t4.score >= 60 AND t4.score < 80 THEN
          '及格'
         WHEN t4.score < 60 THEN
          '不及格'
       END),'没有成绩')) "英语"
  FROM test4 t4
GROUP BY t4.name;

---------------------------------------------------题5
CREATE TABLE test5_yj(
       MONTH VARCHAR(10),
       deptno INT(10),
       yj INT(10)
);

INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('一月份',01,10);
INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('二月份',02,10);
INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('二月份',03,5);
INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('三月份',02,8);
INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('三月份',04,9);
INSERT INTO test5_yj(MONTH,deptno,yj) VALUES('三月份',03,8);

CREATE TABLE test5_dept(
       deptno INT(10),
       dname VARCHAR(20)
);

INSERT INTO test5_dept(deptno,dname) VALUES(01,'国内业务一部');
INSERT INTO test5_dept(deptno,dname) VALUES(02,'国内业务二部');
INSERT INTO test5_dept(deptno,dname) VALUES(03,'国内业务三部');
INSERT INTO test5_dept(deptno,dname) VALUES(04,'国际业务部');

-- 总数据
SELECT * FROM test5_yj;
SELECT * FROM test5_dept;

-- 展示数据
SELECT test5_dept.dname "部门",
       result_yj.yi    "一月份",
       result_yj.e     "二月份",
       result_yj.san   "三月份"
  FROM (SELECT deptno,
               SUM(IF(MONTH='一月份', yj, 0)) yi,
               SUM(IF(MONTH='二月份', yj, 0)) e,
               SUM(IF(MONTH='三月份', yj, 0)) san
          FROM test5_yj
         GROUP BY deptno) result_yj,
       test5_dept
WHERE result_yj.deptno = test5_dept.deptno

iamzhuwenhui

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
zhu的SQL行转列自述（分别用Oracle和Mysql实现）

行转列什么的呵呵。有时候存进去的数据与你要查询并展示的数据是不对应的，某个场景下就需要转换的，这是个苦逼活；有时候面试就会问这个苦逼问题；有时候你就是闲的蛋疼，就会折腾这个东西。题目来自全能的百度，答案自己折腾的，分别用Oracle和Mysql实现。
复制链接

扫一扫