sql温习
/****** Object: Table . Script Date: 06/08/2011 16:01:07 ******/CREATE TABLE .(
(50) NOT NULL,
(50) NULL,
(100) NULL
)
/****** Object: Table . Script Date: 06/08/2011 16:02:32 ******/
CREATE TABLE .(
(50) NOT NULL,
(50) NULL,
NULL,
(50) NULL
)
/****** Object: Table . Script Date: 06/08/2011 16:03:09 ******/
CREATE TABLE .(
(50) NOT NULL,
(50) NULL,
(100) NULL,
(10) NULL
)
--全关联查询
select s.sid,s.sname,s.sgender,c.cname,sc.score
from
student s,class c,score sc
where
s.sid = sc.sid
and
c.cid = sc.cid
--计算每个人的总分并排序
select t1.sid,t1.sname,t1.sgender,t2.allscore from student t1,
(
select sc.sid,SUM(sc.score) as allscore
from score sc,student s
where s.sid = sc.sid
group by sc.sid
)t2
where t1.sid = t2.sid
order by t2.allscore desc
--计算每个人的平均分并排序
select t1.sid,t1.sname,t1.sgender,t2.avgscore from student t1,
(
select sc.sid,AVG(sc.score) as avgscore
from score sc,student s
where s.sid = sc.sid
group by sc.sid
)t2
where t1.sid = t2.sid
order by t2.avgscore desc
--计算每个人的各科成绩，总分，及平均分
select s.sname,
max(case when c.cname='语文' then sc.score else 0 end) as yuwen,
max(case when c.cname='数学' then sc.score else 0 end) as shuxue,
SUM(sc.score) as allscore,
AVG(sc.score) as avgscore
from score sc,class c,student s
where sc.cid = c.cid
and s.sid = sc.sid
group by s.sname
页:
[1]