求ＴＳＱＬ存储过程代码解决方法
求ＴＳＱＬ存储过程代码
求ＴＳＱＬ存储过程代码，过程需要两个参数@zgf（最高分数线）,@zdf(最低分数线)
问题背景：
考试过后，要求统计所有学生的总分分数段，统计出每个班级在每个分数段中所占人数，分数段是由一个最高分数线和最低分数线限定的区间，这两个分之间每１０分为一个档，比如统计550到600之间各段人数，就是分别查出总分>=600的多少人，590<=总分<600的多少人，.......，550<=总分<560的多少人，<550的多少人
假设@zgf＝600,@zdf=550
由下面的表marks查询出下面的表fsd(分数段)
表名：marks（bj班级,xm姓名,zf总分）
bj xm zf
1 a 602
2 b 580
3 c 588
1 d 700
2 e 312
3 f 555
1 g 400
生成下表
表名：fsd（bj班级 fs分数 rs人数 lj累计）
下面bj(班级为０表示全学年，也就是所有班相加，rs是该段人数，lj是该段上不封顶的累计)
bj fs rs lj
0 600 2 2
0 590 0 2
0 580 2 4
0 570 0 4
0 560 0 4
0 550 1 5
0 0 2 7
1 600 2 2
1 590 0 2
1 580 0 2
1 570 0 2
1 560 0 2
1 550 0 2
1 0 1 3
2 600 0 0
2 590 0 0
2 580 1 1
2 570 0 1
2 560 0 1
2 550 0 1
2 0 1 2
3 600 0 0
3 590 0 0
3 580 1 1
3 570 0 1
3 560 0 1
3 550 1 2
3 0 0 2
------解决方案--------------------
求ＴＳＱＬ存储过程代码，过程需要两个参数@zgf（最高分数线）,@zdf(最低分数线)
问题背景：
考试过后，要求统计所有学生的总分分数段，统计出每个班级在每个分数段中所占人数，分数段是由一个最高分数线和最低分数线限定的区间，这两个分之间每１０分为一个档，比如统计550到600之间各段人数，就是分别查出总分>=600的多少人，590<=总分<600的多少人，.......，550<=总分<560的多少人，<550的多少人
假设@zgf＝600,@zdf=550
由下面的表marks查询出下面的表fsd(分数段)
表名：marks（bj班级,xm姓名,zf总分）
bj xm zf
1 a 602
2 b 580
3 c 588
1 d 700
2 e 312
3 f 555
1 g 400
生成下表
表名：fsd（bj班级 fs分数 rs人数 lj累计）
下面bj(班级为０表示全学年，也就是所有班相加，rs是该段人数，lj是该段上不封顶的累计)
bj fs rs lj
0 600 2 2
0 590 0 2
0 580 2 4
0 570 0 4
0 560 0 4
0 550 1 5
0 0 2 7
1 600 2 2
1 590 0 2
1 580 0 2
1 570 0 2
1 560 0 2
1 550 0 2
1 0 1 3
2 600 0 0
2 590 0 0
2 580 1 1
2 570 0 1
2 560 0 1
2 550 0 1
2 0 1 2
3 600 0 0
3 590 0 0
3 580 1 1
3 570 0 1
3 560 0 1
3 550 1 2
3 0 0 2
------解决方案--------------------
- SQL code
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#marks') is null
drop table #marks
Go
Create table #marks([bj] int,[xm] nvarchar(1),[zf] int)
Insert #marks
select 1,N'a',602 union all
select 2,N'b',580 union all
select 3,N'c',588 union all
select 1,N'd',700 union all
select 2,N'e',312 union all
select 3,N'f',555 union all
select 1,N'g',400
Go
declare @zgf int,@zdf int
select @zgf=600,@zdf=550
;with b
as
(select @zgf as grade1,grade2=800
union all
select grade1-10,grade1 from b where grade1>550)
,c as
(select 0 as bj union all select distinct bj from #marks)
,d as
(select * from (Select * from b union all select 0,@zdf) as a ,c)
select
a.bj,a.grade1 as fs,rs=COUNT(case when b.[zf]<grade2 then 1 end),lj=COUNT(b.bj)
from
d as a
left join
#marks as b on b.[zf]>=grade1 and (a.bj=0 or a.bj=b.bj)
group by a.bj,a.grade1
order by a.bj, a.grade1 desc
/*
bj fs rs lj
0 600 2 2
0 590 0 2
0 580 2 4
0 570 0 4
0 560 0 4
0 550 1 5
0 0 2 7
1 600 2 2
1 590 0 2
1 580 0 2
1 570 0 2
1 560 0 2
1 550 0 2
1 0 1 3
2 600 0 0
2 590 0 0
2 580 1 1
2 570 0 1
2 560 0 1
2 550 0 1
2 0 1 2
3 600 0 0
3 590 0 0
3 580 1 1
3 570 0 1
3 560 0 1
3 550 1 2
3 0 0 2
*/
------解决方案--------------------
- SQL code
create table marks(bj int,xm nvarchar(10),zf int) insert into marks select 1,'a',602 insert into marks select 2,'b',580 insert into marks select 3,'c',588 insert into marks select 1,'d',700 insert into marks select 2,'e',312 insert into marks select 3,'f',555 insert into marks select 1,'g',400 go declare @zgf int,@zdf int set @zgf=600 set @zdf=550 select a.bj,a.df,SUM(case when b.zf between a.df and a.gf then 1 else 0 end)rs,COUNT(b.zf)lj from( select a.bj,b.df,b.gf from ( select 0 as bj union select distinct bj from marks )a,( select (case when number>0 then (number-1)*10+@zdf else 0 end)df, (case when (number-1)*10+@zdf>=@zgf then 10000 else number*10+@zdf-1 end)gf from master..spt_values where type='p' and (number-1)*10+@zdf<=@zgf )b)a left join marks b on b.bj=(case when a.bj=0 then b.bj else a.bj end) and b.zf>=a.df-- between a.df and a.gf group by a.bj,a.df order by a.bj,a.df desc /* bj df rs lj ----------- ----------- ----------- ----------- 0 600 2 2 0 590 0 2 0 580 2 4 0 570 0 4 0 560 0 4 0 550 1 5 0 0 2 7 1 600 2 2 1 590 0 2 1 580 0 2 1 570 0 2 1 560 0 2 1 550 0 2 1 0 1 3 2 600 0 0 2 590 0 0 2 580 1 1 2 570 0 1 2 560 0 1 2 550 0 1 2 0 1 2 3 600 0 0 3 590 0 0 3 580 1 1 3 570 0 1 3 560 0 1 3 550 1 2 3 0 0 2 警告: 聚合或其他 SET 操作消除了 Null 值。 (28 行受影响) */ go drop table marks