/****** 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