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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u013632854/article/details/77143802

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

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

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

那么问题来了, 行转列的关键在于:

  • 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
展开阅读全文

没有更多推荐了,返回首页