sql温习

acen.chen

/****** Object:  Table [dbo].[class]    Script Date: 06/08/2011 16:01:07 ******/
CREATE TABLE [dbo].[class](
 [id] [varchar](50) NOT NULL,
 [cid] [varchar](50) NULL,
 [cname] [varchar](100) NULL
)
/****** Object:  Table [dbo].[score]    Script Date: 06/08/2011 16:02:32 ******/
CREATE TABLE [dbo].[score](
 [id] [varchar](50) NOT NULL,
 [cid] [varchar](50) NULL,
 [score] [float] NULL,
 [sid] [varchar](50) NULL
 )
/****** Object:  Table [dbo].[student]    Script Date: 06/08/2011 16:03:09 ******/
CREATE TABLE [dbo].[student](
 [id] [varchar](50) NOT NULL,
 [sid] [varchar](50) NULL,
 [sname] [varchar](100) NULL,
 [sgender] [varchar](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