原發(fā)：
http://hi.baidu.com/yeafee/blog/item/cc377dd978991ee838012fe2.html表內(nèi)存結(jié)構(gòu)及數(shù)據(jù)如下:
CREATE?TABLE?`tscore`?(
????`student`?varchar(20)?default?NULL,
????`subject`?varchar(20)?default?NULL,
????`score`?int(11)?default?NULL
)?ENGINE=MyISAM?DEFAULT?CHARSET=utf8;
/*?Data?for?the?`tscore`?table????(Records?1?-?6)?*/
INSERT?INTO?`tscore`?(`student`,?`subject`,?`score`)?VALUES
????('B',?'Maths',?87),
????('A',?'Maths',?88),
????('C',?'Maths',?86),
????('A',?'Language',?81),
????('B',?'Language',?55),
????('A',?'Chemistry',?77);
根據(jù)下列要求，寫出相應(yīng)的SQL語(yǔ)句：
a.??????統(tǒng)計(jì)每個(gè)學(xué)生所有課程的平均成績(jī)；
select?student,avg(score)?from?tscore?group?by?student;
b.?????統(tǒng)計(jì)每門課程所有學(xué)生的平均成績(jī)；
select?subject,avg(score)?from?tscore?group?by?subject;
c.?????構(gòu)建具有如下結(jié)構(gòu)的結(jié)果集：學(xué)生姓名 A課程成績(jī) B課程成績(jī) C課程成績(jī)。不可以使用子查詢。
Oracle:
select?t.subject,
?????????sum(decode(t.subject,?'A',?t.score,?0))?as?score_a,
?????????sum(decode(t.subject,?'B',?t.score,?0))?as?score_b,
?????????sum(decode(t.subject,?'C',?t.score,?0))?as?score_c
from?????score?t
group?by?t.student
MySQL:
select?student,
sum(case?when?subject='Maths'?then?score?end)?as?score_maths,
sum(case?when?subject='Language'?then?score?end)?as?score_language,
sum(case?when?subject='Chemistry'?then?score?end)????as?score_chemistry
from?tscore
group?by?student;
select?distinct?(a.student),b.score?as?maths,c.score?as?Language,d.score?as?Chemistry
from?tscore?a
left?join?tscore?b?on?a.student=b.student?and?b.subject='Maths'
left?join?tscore?c?on?a.student=c.student?and?c.subject='Language'
left?join?tscore?d?on?a.student=d.student?and?d.subject='Chemistry'
order?by?a.student;
d.?????假設(shè)60分為及格，統(tǒng)計(jì)每門課程參加考試的學(xué)生的數(shù)量，及格的學(xué)生數(shù)，以及通過(guò)率（在一條SQL語(yǔ)句中）
Oracle:
select?a.*,?a.pass_count/a.total_count?as?pass_rate
from?(select????t.subject,?count(t.subject)?as?total_count,?sum(decode(sign(t.score-60),1,1[,0,1,]0))?as?pass_count
from??????score?t
group?????by?t.subject)?a
MySQL:
select?a.*,a.passCount/a.totalCount?as?passRate?from
(select?subject,COUNT(subject)?as?totalCount,sum(case?when?(sign(score-60)>=0)?then?1?end)?as?passCount
from?tscore
group?by?subject
)a;
Oracle SQL 內(nèi)置函數(shù)大全(一)、(二)、(三)
SIGN
取數(shù)字n的符號(hào),大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100)?? SIGN(0)
--------- ---------- ---------
1???????? -1???????? 0
INSERT INTO .. .SELECT...FROM復(fù)制
CREATE?TABLE?tmp_user?AS?select?*?from?user?where?1=2;
INSERT INTO user SELECT * FROM tmp_user;