原发：
http://hi.baidu.com/yeafee/blog/item/cc377dd978991ee838012fe2.html表内存结构及数据如下:
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);
根据下列要求，写出相应的SQL语句：
a. 统计每个学生所有课程的平均成绩；
select student,avg(score) from tscore group by student;
b. 统计每门课程所有学生的平均成绩；
select subject,avg(score) from tscore group by subject;
c. 构建具有如下结构的结果集：学生姓名 A课程成绩 B课程成绩 C课程成绩。不可以使用子查询。
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. 假设60分为及格，统计每门课程参加考试的学生的数量，及格的学生数，以及通过率（在一条SQL语句中）
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 内置函数大全(一)、(二)、(三)
SIGN
取数字n的符号,大于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复制
CREATE TABLE tmp_user AS select * from user where 1=2;
INSERT INTO user SELECT * FROM tmp_user;