SQL查询难题(高手快来啊)

[splory]

我建的表如下
table sc (stuid int,subjectid int,score float)
table stu (stuid int,stuname varchar(10),classid int)
table subject (subjectid int,subjectname varchar(10))
你们插入些数据结成
做如下查询,我写了一段了,实现的功能还不全面
-----------------------------------------------------------------------------------------
select (select classid from stu where stu.stuid=sc.stuid) [class],sum(score) 总成绩 into # from sc group by stuid

declare @i0 float,@i1 float,@i2 float,@i3 float,@i4 float,@i5 float,@i6 float,@i7 float,@i8 float,@i9 float,@i10 float
select @i0=avg(总成绩) from # where 总成绩>=660 and 总成绩<=1120
select @i1=avg(总成绩) from # where 总成绩>=650 and 总成绩<=659
select @i2=avg(总成绩) from # where 总成绩>=640 and 总成绩<=649
select @i3=avg(总成绩) from # where 总成绩>=630 and 总成绩<=639
select @i4=avg(总成绩) from # where 总成绩>=620 and 总成绩<=629
select @i5=avg(总成绩) from # where 总成绩>=610 and 总成绩<=619
select @i6=avg(总成绩) from # where 总成绩>=600 and 总成绩<=609
select @i7=avg(总成绩) from # where 总成绩>=550 and 总成绩<=599
select @i8=avg(总成绩) from # where 总成绩>=500 and 总成绩<=549
select @i9=avg(总成绩) from # where 总成绩>=400 and 总成绩<=499
select @i10=avg(总成绩) from # where 总成绩>=0 and 总成绩<=399

select '1120-660' 分数段,@i0 段均分,cast(sum(case when 总成绩>=660 and 总成绩<=1120 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=660 then 1 else 0 end) as nvarchar) [年段] from #
union
select '659-650' ,@i1 ,cast(sum(case when 总成绩>=650 and 总成绩<=659 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=650 then 1 else 0 end) as nvarchar) from #
union
select '649-640' ,@i2 ,cast(sum(case when 总成绩>=640 and 总成绩<=649 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=640 then 1 else 0 end) as nvarchar) from #
union
select '639-630' ,@i3 ,cast(sum(case when 总成绩>=630 and 总成绩<=639 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=630 then 1 else 0 end) as nvarchar) from #
union
select '629-620' ,@i4 ,cast(sum(case when 总成绩>=620 and 总成绩<=629 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=620 then 1 else 0 end) as nvarchar) from #
union
select '619-610' ,@i5 ,cast(sum(case when 总成绩>=610 and 总成绩<=619 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=610 then 1 else 0 end) as nvarchar) from #
union
select '609-600' ,@i6 ,cast(sum(case when 总成绩>=600 and 总成绩<=609 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=600 then 1 else 0 end) as nvarchar) from #
union
select '599-550' ,@i7 ,cast(sum(case when 总成绩>=550 and 总成绩<=599 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=550 then 1 else 0 end) as nvarchar) from #
union
select '549-500' ,@i8 ,cast(sum(case when 总成绩>=500 and 总成绩<=549 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=500 then 1 else 0 end) as nvarchar) from #
union
select '499-400' ,@i9 ,cast(sum(case when 总成绩>=400 and 总成绩<=499 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=400 then 1 else 0 end) as nvarchar) from #
union
select '399-0' ,@i10 ,cast(sum(case when 总成绩>=0 and 总成绩<=399 then 1 else 0 end) as nvarchar)+'/'+cast(sum(case when 总成绩>=0 then 1 else 0 end) as nvarchar) from #

drop table #
-----------------------------------------------------------------------------------------
只能得到了年级的,但还想对各个班的在每个分数段的人数,得到的结果要跟年段的类似,这就需要加代码,高手来解决阿,最好把我写的代码也给简化一下(想得到的结果如下)
分数段 段均分 1班 2班 3班 年段
499-400 488.5 2/6 3/4 1/5 6/15

[-狙击手-]

分数段没有规律啊？？？？

[splory]

是啊,分数段要有个规律就不是很现实了阿
我写的结果只是一部分,不要受的我的影响
我是为了给个事例而已
结果你们自己运行一下就知道了

[splory]

大家怎么这么不给面子阿
问题没人来解决呢？！

[splory]

终于有人回复了，谢谢了，但我的意思是对班级的数目是动态的，在这种情况下把所有班级的统计情况都统计出来

[Mryu666]

sc 成绩表(学生ID,科目ID，成绩)
stu 学生表（ID,名称，班级ID）
subject 科目表（ID,科目名称）
是不是少个班级表啊！
你的表是不是这个意思啊！

[Yang_]

--静态的

select classid,stuid,sum(score) as score into #
from sc,stu
where sc.stuid=stu.stuid
group by classid,stuid


select '0-399' as 分数段,
avg(score) as 段均分,
cast(sum(case when classid=1 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=1) as nvarchar) as [1班],
cast(sum(case when classid=2 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=2) as nvarchar) as [2班],
cast(sum(case when classid=3 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=3) as nvarchar) as [3班],
cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<400
union all
select '400-499' as 分数段,
avg(score) as 段均分,
cast(sum(case when classid=1 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=1) as nvarchar) as [1班],
cast(sum(case when classid=2 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=2) as nvarchar) as [2班],
cast(sum(case when classid=3 then 1 else 0 end) as nvarchar)+'/'+cast((select count(*) from # where classid=3) as nvarchar) as [3班],
cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<500 and score>=400
...

drop table #

[Yang_]

--动态的

select classid,stuid,sum(score) as score into #
from sc,stu
where sc.stuid=stu.stuid
group by classid,stuid

declare @sql varchar（8000）

set @sql=',avg(score) as 段均分'
select @sql=@sql+',cast(sum(case when classid='+cast(classid as varchar(10))+' then 1 else 0 end) as nvarchar)+''/''+cast((select count(*) from # where classid='+cast(classid as varchar(10))+') as nvarchar) as ['+cast(classid as varchar(10))+'班]'
from #
group by classid
order by classid

exec ('select ''0-399'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<400
union all
select ''400-499'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<500 and score>=400
union all
select ''500-549'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<550 and score>=500
union all
select ''550-599'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<600 and score>=550
union all
select ''600-609'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<610 and score>=600
union all
select ''610-619'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<620 and score>=610
union all
select ''620-629'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<630 and score>=620
union all
select ''630-639'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<640 and score>=630
union all
select ''640-649'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<650 and score>=640
union all
select ''650-659'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score<660 and score>=650
union all
select ''660-1120'' as 分数段'+@sql+',cast(count(*) as nvarchar)+'/'+cast((select count(*) from #) as nvarchar) as [年段]
from #
where score>=660
')

drop table #

[Yang_]

我没环境，自己测试下，有问题再说

[Jane_64]

create table sc (stuid int,subjectid int,score float)
create table stu (stuid int,stuname varchar(10),classid int)
create table subject (subjectid int,subjectname varchar(10))
/*将成绩段放在表里*/
create table 成绩段 (id smallint,begScore float,endScore float)

insert sc
select 1,11,99 union
select 1,12,98 union
select 1,13,95 union
select 2,11,94 union
select 2,12,97 union
select 2,13,95 union
select 3,14,255 union
select 3,15,365


insert stu
select 1,'aa',1 union
select 2,'bb',1 union
select 3,'cc',2

insert 成绩段
select 1,660,1120 union
select 2,650,659 union
select 3,640,649 union
select 4,630,639 union
select 5,620,629 union
select 6,610,619 union
select 7,600,609 union
select 8,550,599 union
select 9,500,549 union
select 10,400,499 union
select 11,0,399


select classid,stu.stuid,sum(score) 总成绩
into #tmp
from stu,sc
where stu.stuid=sc.stuid
group by classid,stu.stuid

/*按年级*/
declare @sql varchar(8000)
set @sql = 'select convert(varchar,endScore)+''-''+convert(varchar,begScore) 分数段,
avg(总成绩) 段均分'
select @sql = @sql+',['+ convert(varchar,classid)+'班]=
convert(varchar,sum(case when convert(varchar,classid)='+convert(varchar,classid)+
' and 总成绩 between begScore and endScore then 1 else 0 end))+''/''
+convert(varchar,sum(case when convert(varchar,classid)='+convert(varchar,classid)+
' and 总成绩 >= begScore then 1 else 0 end))'
from #tmp group by classid
select @sql = @sql+',convert(varchar,sum(case when 总成绩 between begScore and endScore then 1 else 0 end))+''/''
+convert(varchar,sum(case when 总成绩 >= begScore then 1 else 0 end)) 年段
from 成绩段,#tmp
where 总成绩 between begScore and endScore
group by convert(varchar,endScore)+''-''+convert(varchar,begScore)'

exec(@sql)


/*按班*/
select classid,
convert(varchar,endScore)+'-'+convert(varchar,begScore) 分数段,
avg(总成绩) 段均分,
convert(varchar,sum(case when 总成绩 between begScore and endScore then 1 else 0 end))+'/'+
convert(varchar,sum(case when 总成绩 >= begScore then 1 else 0 end)) 班段
from 成绩段,#tmp
where 总成绩 between begScore and endScore
group by classid,convert(varchar,endScore)+'-'+convert(varchar,begScore)

[splory]

就没有高手愿意来帮忙说
哎，世态炎凉阿！

[mschen]

--这样看看.

--创建测试表,插入测试数据----------------------

create table sc (stuid int,subjectid int,score float)
create table stu (stuid int,stuname varchar(10),classid int)
create table subject (subjectid int,subjectname varchar(10))

insert sc select 1,11,99
insert sc select 1,12,98
insert sc select 1,13,95
insert sc select 2,11,94
insert sc select 2,12,97
insert sc select 2,13,95
insert sc select 1,14,255
insert sc select 1,15,365


insert stu select 1,'aa',111
insert stu select 2,'bb',112
insert stu select 3,'cc',111


--创建各学生总成绩的视图-----------------------
create view dbo.v_SumScore
as
select stuid,sum(score) as sum_score

from sc

group by stuid
Go

--查询------------------------------------------
select 分数段=cast(t.sc_end as varchar)+'-'+cast(t.sc_start as varchar),
t.段均分,
[1班]=max((case 班级 when 111 then cast(人数 as varchar)+'/'+cast(班级总人数 as varchar) end)),
[2班]=max((case 班级 when 112 then cast(人数 as varchar)+'/'+cast(班级总人数 as varchar) end))
from
(
select a.sc_start ,
a.sc_end ,
c.classid as 班级,
count(*) as 人数,
(select count(*) from stu where classid=c.classid) as 班级总人数,
(select avg(sum_score) from dbo.v_SumScore where sum_score between a.sc_end and a.sc_start) as 段均分
from #t a join dbo.v_SumScore b
on b.sum_score between a.sc_end and a.sc_start
join stu c
on b.stuid=c.stuid
group by a.sc_start,a.sc_end,c.classid
)t
group by cast(t.sc_end as varchar)+'-'+cast(t.sc_start as varchar),t.段均分

--结果--------------------------------------

分数段 段均分 1班 2班
------------------------------------------------------------- ----------------------------------------------------- ------------------------------------------------------------- -------------------------------------------------------------
0-399 286.0 NULL 1/1
660-1120 912.0 1/2 NULL

（所影响的行数为 2 行）

警告: 聚合或其它 SET 操作消除了空值。

[llh6795]

学习

[Mryu666]

--创建数据表
CREATE table #sc (stuid int,subjectid int,score float)
CREATE table #stu (stuid int,stuname varchar(10),classid int)
--没有用到CREATE table #subject (subjectid int,subjectname varchar(10))
CREATE table #Class (ClassID int,ClassName varchar(10))
declare @i int,@n int,@m int ,@1A float,@1B float,@SQL varchar(4000)
--添加测试数据
--添加成绩数据99个学生6门功课成绩
set @i =1
WHILE (@i < 100) --学生循环
begin
set @n =1
WHILE (@n < 7) --科目
begin
insert into #sc values(@i,@n,80+@n+floor(@i/10))
set @n=@n+1
end
set @i=@i+1
end
--添加学生数据99个学生
set @i =1
WHILE (@i < 100) --学生循环
begin
insert into #stu values(@i,'张'+convert(varchar(2),@i),floor(@i/7)+1)
set @i=@i+1
end
--添加班级数据 15个班级
--因为不知道班级表什么样自己起名字了
set @i =1
set @n =1
set @m=1
while @i<=15
begin
insert into #Class values(@i,convert(varchar(2),@i)+'班')
set @i=@i+1
end
--把先统计出来的信息插入到一个临时表里！为了方便你可以建立一个视图
select * into #tt
from (select sum(总成绩) as 总成绩,(select ClassName from #Class c where c.ClassID=b.ClassID) as ClassID,ClassID as PX,分数段,count(*) as 个数 from
(select sum(Score) as 总成绩,(Case When sum(Score)>660 then '1200-660'
When sum(Score) between 600 and 659 then '659-600'
When sum(Score) between 550 and 599 then '599-550'
When sum(Score) between 535 and 549 then '549-535'
When sum(Score) between 450 and 534 then '534-450'
When sum(Score) between 300 and 349 then '349-300'
When sum(Score) between 0 and 299 then '299-0'
end ) as 分数段,
stuid from #sc group by stuid)a inner join #stu b on a.stuid=b.stuid
group by ClassID,分数段)a

--割据数据动态组合查询语句
set @sql='SELECT 分数段,convert(decimal(18,3),sum(总成绩)/sum(个数)) as 平均分,convert(varchar,sum(个数))+'
+char(39)+'/'+char(39)+'+convert(varchar,(select sum(个数) from #tt)) as 年段'
select @sql=@sql+',convert(varchar,sum(case when ClassID='''+ClassID+''' then 个数 else 0 end))+'+char(39)+'/'+char(39)
+'+convert(varchar,(select sum(个数) from #tt where ClassID='''+ClassID+''' group by ClassID)) as '''+ClassID+''''
from #tt
group by ClassID,PX order by PX
set @sql=@sql+' from #tt group by 分数段'
--执行组合完的查询语句
exec(@Sql)

--删除临时表
drop Table #sc
drop Table #stu
drop Table #Class
drop Table #TT


-- 结果
分数段 平均分 年段 1班 2班 3班 4班 。。。。。。
534-450 516.254 59/99 6/6 7/7 7/7 7/7 7/7 7/7
549-535 543.000 30/99 0/6 0/7 0/7 0/7 0/7 0/7
599-550 555.000 10/99 0/6 0/7 0/7 0/7 0/7 0/7

[splory]

Yang_(扬帆破浪) 的查询结果为
分数段 段均分 1班 2班 年段
660-1120 NULL NULL NULL 0/7
650-659 NULL NULL NULL 0/7
640-649 NULL NULL NULL 0/7
630-639 NULL NULL NULL 0/7
620-629 NULL NULL NULL 0/7
610-619 NULL NULL NULL 0/7
600-609 NULL NULL NULL 0/7
550-599 NULL NULL NULL 0/7
500-549 522.40 4/6 1/1 5/7
400-499 485.50 2/6 0/1 2/7
0-399 NULL NULL NULL 0/7

结果有点不对啊
我想要的是本分段有多少人/本分数段加本分叔段上的人数的这样的一个结果
你得到的却是本分数段/总的人数的这样的一个结果
老大再帮我改改拉
谢谢

[splory]

上面回帖的得到的结果都是那样阿,这样不成啊