求ＴＳＱＬ存储过程代码
求ＴＳＱＬ存储过程代码，过程需要两个参数@zgf（最高分数线）,@zdf(最低分数线)
问题背景：
考试过后，要求统计所有学生的总分分数段，统计出每个班级在每个分数段中所占人数，分数段是由一个最高分数线和最低分数线限定的区间，这两个分之间每１０分为一个档，比如统计550到600之间各段人数，就是分别查出总分>=600的多少人，590<=总分<600的多少人，.......，550<=总分<560的多少人，<550的多少人
假设@zgf＝600,@zdf=550
由下面的表marks查询出下面的表fsd(分数段)
表名：marks（bj班级,xm姓名,zf总分）
bjxmzf
1a602
2b580
3c588
1d700
2e312
3f555
1g400
生成下表
表名：fsd（bj班级 fs分数rs人数lj累计）
下面bj(班级为０表示全学年，也就是所有班相加，rs是该段人数，lj是该段上不封顶的累计)
bjfsrslj
060022
059002
058024
057004
056004
055015
0027
160022
159002
158002
157002
156002
155002
1013
260000
259000
258011
257001
256001
255001
2012
360000
359000
358011
357001
356001
355012
3002
[解决办法]
--> --> (Roy)生成測試數據 if not object_id('Tempdb..#marks') is null drop table #marksGoCreate table #marks([bj] int,[xm] nvarchar(1),[zf] int)Insert #marksselect 1,N'a',602 union allselect 2,N'b',580 union allselect 3,N'c',588 union allselect 1,N'd',700 union allselect 2,N'e',312 union allselect 3,N'f',555 union allselect 1,N'g',400Godeclare @zgf int,@zdf intselect @zgf=600,@zdf=550;with bas(select @zgf as grade1,grade2=800union allselect 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 aleft join #marks as b on b.[zf]>=grade1 and (a.bj=0 or a.bj=b.bj) group by a.bj,a.grade1order by a.bj, a.grade1 desc/*bj fs rs lj0 600 2 20 590 0 20 580 2 40 570 0 40 560 0 40 550 1 50 0 2 71 600 2 21 590 0 21 580 0 21 570 0 21 560 0 21 550 0 21 0 1 32 600 0 02 590 0 02 580 1 12 570 0 12 560 0 12 550 0 12 0 1 23 600 0 03 590 0 03 580 1 13 570 0 13 560 0 13 550 1 23 0 0 2*/
[解决办法]
create table marks(bj int,xm nvarchar(10),zf int)insert into marks select 1,'a',602insert into marks select 2,'b',580insert into marks select 3,'c',588insert into marks select 1,'d',700insert into marks select 2,'e',312insert into marks select 3,'f',555insert into marks select 1,'g',400godeclare @zgf int,@zdf intset @zgf=600set @zdf=550select 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 bjunionselect 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)gffrom 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.gfgroup by a.bj,a.dforder by a.bj,a.df desc/*bj df rs lj----------- ----------- ----------- -----------0 600 2 20 590 0 20 580 2 40 570 0 40 560 0 40 550 1 50 0 2 71 600 2 21 590 0 21 580 0 21 570 0 21 560 0 21 550 0 21 0 1 32 600 0 02 590 0 02 580 1 12 570 0 12 560 0 12 550 0 12 0 1 23 600 0 03 590 0 03 580 1 13 570 0 13 560 0 13 550 1 23 0 0 2警告: 聚合或其他 SET 操作消除了 Null 值。(28 行受影响)*/godrop table marks