求ＴＳＱＬ存储过程代码，[email protected]（最高分数线）,@zdf(最低分数线)
问题背景：
考试过后，要求统计所有学生的总分分数段，统计出每个班级在每个分数段中所占人数，分数段是由一个最高分数线和最低分数线限定的区间，这两个分之间每１０分为一个档，比如统计550到600之间各段人数，就是分别查出总分>=600的多少人，590<=总分<600的多少人，.......，550<=总分<560的多少人，<550的多少人
[email protected]＝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 #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*/
------解决方案--------------------
- SQL code
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)[email protected] else 0 end)df,(case when (number-1)[email protected]>[email protected] then 10000 else [email protected] end)gffrom master..spt_values where type='p' and (number-1)[email protected]<[email protected])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