Mysql04(行专列)

短命女 2021-09-08 00:44 191阅读 0赞

## Mysql04(行专列) ##

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

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