SQL学习笔记04-- sql的行转列

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','负');

/*
          胜 负
2005-05-09 2 2
2005-05-10 1 2

*/

select rq,decode(shengfu,'胜',1),decode(shengfu,'负',2) from tmp;

select rq,
       count(decode(shengfu, '胜', 1)) 胜,
       count(decode(shengfu, '负', 2)) 负
  from tmp
 group by rq;


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);


/*
姓名   语文  数学  英语
王五    89    56    89
*/
--至少使用4中方式下写出
--decode
select ss.name,
       max(decode(ss.subject, '语文', ss.score)) 语文,
       max(decode(ss.subject, '数学', ss.score)) 数学,
       max(decode(ss.subject, '英语', ss.score)) 英语
  from student_score ss group by ss.name
--case when
select ss.name,
       max(case ss.subject
             when '语文' then
              ss.score
           end) 语文,
       max(case ss.subject
             when '数学' then
              ss.score
           end) 数学,
       max(case ss.subject
             when '英语' then
              ss.score
           end) 英语
  from student_score ss
 group by ss.name;
--join
select ss.name,ss.score from student_score ss where ss.subject='语文';
select ss.name,ss.score from student_score ss where ss.subject='数学';
select ss.name,ss.score from student_score ss where ss.subject='英语';

select ss01.name, ss01.score 语文, ss02.score 数学, ss03.score 英语
  from (select ss.name, ss.score
          from student_score ss
         where ss.subject = '语文') ss01
  join (select ss.name, ss.score
          from student_score ss
         where ss.subject = '数学') ss02
    on ss01.name = ss02.name
  join (select ss.name, ss.score
          from student_score ss
         where ss.subject = '英语') ss03
    on ss01.name = ss03.name;

--union all
select t.name,sum(t.语文),sum(t.数学),sum(t.英语) from (select ss01.name,ss01.score 语文,0 数学,0 英语 from student_score ss01 where ss01.subject='语文' union all
select ss02.name,0 语文,ss02.score 数学,0 英语 from student_score ss02 where ss02.subject='数学' union all
select ss03.name,0 语文,0 数学,ss03.score 英语 from student_score ss03 where ss03.subject='英语') t group by t.name



==================================================================================
/* 中国移动sql面试题：*/
create table test1(
   id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(5)
);
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
*/

select max(decode(ts1.type, 1, ts1.value)) 姓名,
       max(decode(ts1.type, 2, ts1.value)) 性别,
       max(decode(ts1.type, 3, ts1.value)) 年龄
  from test1 ts1
 group by ts1.t_id having ts1.t_id =1 ;

/*
一道SQL语句面试题，关于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
------------------------------------------
*/
create table tmp1(rq varchar2(10),shengfu varchar2(5));

insert into tmp1 values('2005-05-09','胜');
insert into tmp1 values('2005-05-09','胜');
insert into tmp1 values('2005-05-09','负');
insert into tmp1 values('2005-05-09','负');
insert into tmp1 values('2005-05-10','胜');
insert into tmp1 values('2005-05-10','负');
insert into tmp1 values('2005-05-10','负');

select tp1.rq,decode(tp1.shengfu,'胜',1),decode(tp1.shengfu,'负',0) from tmp1 tp1

select tp1.rq,
       count(decode(tp1.shengfu, '胜', 1)) 胜,
       count(decode(tp1.shengfu, '负', 0)) 负
  from tmp1 tp1
 group by tp1.rq
 order by tp1.rq;

/*
4.1得到类似下面的结果
姓名   语文  数学  英语

王五    89    56    89

4.2有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  
   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  
       显示格式：  
       语文              数学                英语  
       及格              优秀                不及格  
       */
create table STUDENT_SCORE1
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score1 (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

select ss.name,
       max(decode(ss.subject, '语文', ss.score)) 语文,
       max(decode(ss.subject, '数学', ss.score)) 数学,
       max(decode(ss.subject, '英语', ss.score)) 英语
  from student_score1 ss group by ss.name having ss.name='王五'
  
select ss.name name,
       max(case ss.subject
             when '语文' then
               (case 
                 when(ss.score>=80) then
                   '优秀'
                 when(ss.score>=60) then
                   '及格'
                 when(ss.score<60) then
                   '不及格'
                 end  )            
           end) 语文,
       max(case ss.subject
             when '数学' then
              (case 
                 when(ss.score>=80) then
                   '优秀'
                 when(ss.score>=60) then
                   '及格'
                 when(ss.score<60) then
                   '不及格'
                 end  )   
           end) 英语,
       max(case ss.subject
             when '英语' then
              (case 
                 when(ss.score>=80) then
                   '优秀'
                 when(ss.score>=60) then
                   '及格'
                 when(ss.score<60) then
                   '不及格'
                 end  )   
           end) 英语
  from student_score1 ss
 group by ss.name;



/*请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，
只是作为一个格式向大家请教。
 

table1

月份mon 部门dep 业绩yj
-------------------------------
一月份      01      10
一月份      02      10
一月份      03      5
二月份      02      8
二月份      04      9
三月份      03      8

table2

部门dep      部门名称dname
--------------------------------
      01      国内业务一部
      02      国内业务二部
      03      国内业务三部
      04      国际业务部

table3 （result）

部门dep 一月份      二月份      三月份
--------------------------------------
      01      10                  
      02      10         8         
      03                 5        8
      04                          9

------------------------------------------
*/
create table yj01(
       month varchar2(10),
       deptno number(10),
       yj number(10)
)

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

create table yjdept(
       deptno number(10),
       dname varchar2(20)
)

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

select yj01.deptno,
       sum(decode(yj01.month, '一月份', yj01.yj)) 一月份,
       sum(decode(yj01.month, '二月份', yj01.yj)) 二月份,
       sum(decode(yj01.month, '三月份', yj01.yj)) 三月份
  from yj01
 group by yj01.deptno 
 order by yj01.deptno;
 
 select yjdept.dname,yj02.one,yj02.two,yj02.three from
       (select yj01.deptno deptno,sum(decode(yj01.month, '一月份', yj01.yj)) one,
       sum(decode(yj01.month, '二月份', yj01.yj)) two,
       sum(decode(yj01.month, '三月份', yj01.yj)) three
  from yj01
 group by yj01.deptno 
 order by yj01.deptno) yj02 join  yjdept on  yj02.deptno=yjdept.deptno;