一。
表str
-------------------------------------------------
学号(id) 班级(class) 分数(score)
-------------------------------------------------
问题:sql
1 列出每个班的平均成绩
2 列出比本班平均成绩高的学生的学好、班级、班级平均成绩。二.
表msg
--------------
2004 1000
2005 2000
2006 3000
2007 4000
--------------
查询结果2004 1000
2005 3000
2006 6000
2007 10000
问:
写出查询的结果的sql?
表str
-------------------------------------------------
学号(id) 班级(class) 分数(score)
-------------------------------------------------
问题:sql
1 列出每个班的平均成绩
2 列出比本班平均成绩高的学生的学好、班级、班级平均成绩。二.
表msg
--------------
2004 1000
2005 2000
2006 3000
2007 4000
--------------
查询结果2004 1000
2005 3000
2006 6000
2007 10000
问:
写出查询的结果的sql?
if object_id('[msg]') is not null drop table [msg]
create table [msg]([a] int,[b] int)
insert [msg]
select 2004,1000 union all
select 2005,2000 union all
select 2006,3000 union all
select 2007,4000select a,b=isnull(b+(select sum(b) from msg where t.a>a),b) from [msg] t/*
a b
----------- -----------
2004 1000
2005 3000
2006 6000
2007 10000(所影响的行数为 4 行)
*/drop table msg
select class,ag=avg(score) from [str] group by class)t on t.class=s.class
where s.score>=ag
select id,class,ag from [str] s join (
select class,ag=avg(score) from [str] group by class)t on t.class=s.class
where s.score>=ag
id,class,score
from [str] a
cross apply
(select avg(score)avgScore from [str] where class=a.class )b
where a.score>=b.avgScore
declare @str table(id int,class nvarchar(10),score float)
insert into @str select 1,'A',90
insert into @str select 2,'A',80
insert into @str select 3,'A',70
insert into @str select 4,'B',100
insert into @str select 5,'B',96
insert into @str select 6,'B',88select class,avg(score) as avgscore from @str group by classselect * from @str a join (select class,avg(score) as avgscore from @str group by class) b on a.class=b.class
where a.score>avgscore
declare @msg table(year int,number int)
insert into @msg select 2004,1000
insert into @msg select 2005,2000
insert into @msg select 2006,3000
insert into @msg select 2007,4000select year,number=isnull((select sum(number) from @msg b where b.year<=a.year),0) from @msg a
group by class
select id,class,avg(score) from [str] t
where score>(select class,avg(score) 平均成绩 from [str] where id=t.id
group by class)
group by id,class
declare @t table(id int,class varchar(50),score int)
insert into @t select '1','0901','85'
insert into @t select '2','0902','41'
insert into @t select '3','0901','55'
insert into @t select '4','0902','99'
insert into @t select '5','0901','75'
insert into @t select '6','0902','65'
insert into @t select '7','0901','88'
insert into @t select '8','0902','77'
insert into @t select '9','0901','56'select class,avg(score) as avgscore from @t group by class/*-------------------------------------
class avgscore
0901 71
0902 70
---------------------------------------*/select a.id,a.class,b.avgscore from @t a join
(select class,avg(score) as avgscore from @t c group by class) b
on a.class=b.class
where a.score>b.avgscore/*-------------------------------------
id class avgscore
5 0901 71
1 0901 71
7 0901 71
8 0902 70
4 0902 70
---------------------------------------*/