Mysql04(行专列)

专列)"/>

Mysql04(行专列)

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 tmpgroup 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.subjectwhen '语文' thenss.scoreend) 语文,max(case ss.subjectwhen '数学' thenss.scoreend) 数学,max(case ss.subjectwhen '英语' thenss.scoreend) 英语from student_score ssgroup 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.scorefrom student_score sswhere ss.subject = '语文') ss01join (select ss.name, ss.scorefrom student_score sswhere ss.subject = '数学') ss02on ss01.name = ss02.namejoin (select ss.name, ss.scorefrom student_score sswhere ss.subject = '英语') ss03on 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 tmpgroup 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.subjectwhen '语文' thenss.scoreend) 语文,max(case ss.subjectwhen '数学' thenss.scoreend) 数学,max(case ss.subjectwhen '英语' thenss.scoreend) 英语from student_score ssgroup 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.scorefrom student_score sswhere ss.subject = '语文') ss01join (select ss.name, ss.scorefrom student_score sswhere ss.subject = '数学') ss02on ss01.name = ss02.namejoin (select ss.name, ss.scorefrom student_score sswhere ss.subject = '英语') ss03on 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