zhu的SQL行转列自述（分别用Oracle和Mysql实现）
来源：互联网 发布：C语言next_permutation 编辑：程序博客网 时间：2024/06/06 13:21
有时候存进去的数据与你要查询并展示的数据是不对应的，某个场景下就需要转换的，这是个苦逼活
有时候面试就会问这个苦逼问题
有时候你就是闲的蛋疼，就会折腾这个东西
那么问题来了， 行转列的关键在于：
1、找到分组关键
- 需求一般是这样：找到每个人的每科成绩并展示，找到每个月的数据并展示等
你看，都要分组，每个人是一组，每个月也是一组，所以关键在于找到分组的字段
分组的作用在于将相关联的数据整合到一行
- 需求一般是这样：找到每个人的每科成绩并展示，找到每个月的数据并展示等
2、妙用分组函数
- 使用了分组，在select后面就不能随便加内容了，必须跟分组函数，这是语法规定的
只需要单纯展示内容，可以使用max()函数包裹内容，因为你max之后也是你自己，这个是重点
如果是算总数，可以使用sum
如果是统计次数，可以使用count
- 使用了分组，在select后面就不能随便加内容了，必须跟分组函数，这是语法规定的
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就会挤到一行去了- 以上面截图为例，你以T_ID分组了，但是数据怎么取，VALUE里面包含了我要展示的所有内容
说多了都是泪，上题：
（题目来自全能的百度，答案自己折腾的，分别用Oracle和Mysql实现）
一、使用Oracle
/*------------------------------------------经典面试题--1create 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 test1group by T_ID/*------------------------------------------经典面试题--2create 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 22005-05-10 1 2*/--总数据select * from test2;--展示select t2.rq "日期", count(decode(t2.sf, '胜', 1)) "胜"，count(decode(t2.sf, '负', 1)) "负" from test2 t2group by t2.rqorder by t2.rq/*------------------------------------------经典面试题--3create 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;/*------------------------------------------经典面试题--4create 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 t4group 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 t4group 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;/*------------------------------------------经典面试题--5create 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 8test5_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 dwhere result.deptno = d.deptnoorder 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_deptwhere result_yj.deptno = test5_dept.deptno二、使用Mysql
---------------------------------------------------题1CREATE 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 test1GROUP BY T_ID---------------------------------------------------题2CREATE 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 t2GROUP BY t2.rqORDER BY t2.rq---------------------------------------------------题3CREATE 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;---------------------------------------------------题4CREATE 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 t4GROUP 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 t4GROUP BY t4.name;---------------------------------------------------题5CREATE 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_deptWHERE result_yj.deptno = test5_dept.deptno阅读全文
0 0
- zhu的SQL行转列自述（分别用Oracle和Mysql实现）
- 如何实现插入时如果不存在则插入如果存在则更新的操作（分别用oracle、MySQL和SQL Server实现）
- 怎么用Oracle和MySql分别编写？
- zhu的Oracle数据库笔记
- Oracle和Mysql分别生成sequence序列
- Oracle使用goldengate分别向Oracle和mysql双路的单向复制
- MyBatis 分别结合 SQL Server、MySQL、ORACLE进行数据库分页
- 用jsp分别连接oracle、mysql数据库
- oracle，mysql，sql server分页的实现
- 3大数据库(Sql-Server,MySql和Oracle)的分页SQL语句实现
- 3大数据库(Sql-Server,MySql和Oracle)的分页SQL语句实现
- 3大数据库(Sql-Server,MySql和Oracle)的分页SQL语句实现
- 分别用Java、Oracle实现九九乘法表
- Oracle中sql sqlplus pl/sql分别指的是什么
- mysql和oracle的sql语法
- #DayOne#,分别用循环和递归的方式实现阶乘
- 用线程和借口分别实现的Java秒表
- 用LinearLayout和RelativeLayout分别实现简单的登陆界面
- 查询优化-索引的使用
- 冒泡排序算法（九）
- 小小粉丝度度熊
- POJ 题目分类
- Redis HyperLogLog
- zhu的SQL行转列自述（分别用Oracle和Mysql实现）
- ajax方法的封装
- 引起高血压的药
- 应届毕业生如何通过学习Linux系统选择一份高薪职业
- 各种排序算法的总结
- linux实验楼学习笔记7
- c++/c常量
- Google大数据论文三宝之BigTable
- OpenCV学习之利用级联的haar分类器寻找检测目标
