分类汇总

最新推荐文章于 2024-06-30 18:41:59 发布

zhiguo2008

最新推荐文章于 2024-06-30 18:41:59 发布

阅读量481

点赞数

分类专栏：数据库类文章标签： table function insert object float null

本文链接：https://blog.csdn.net/zhiguo2008/article/details/2679717

版权

数据库类专栏收录该内容

4 篇文章 0 订阅

订阅专栏

create table (id int,bj nvarchar(6),zf float) 
insert table values select 1,'1',701 
union 
select 2,'2',702 
union 
select 3,'1',681 
union 
select 4,'2',501 
union 
select 5,'1',401 
union 
select 6,'1',101 
 
分数段  计1 累1 计2 累2 总计 总累 
700    1  1 
690    0  1 
680    1  2 
.      .  . 
.      .  . 
300    0  
其余    1 
举例说明： 
红色1表示1班在680 <=zf <690范围有1名学生 
绿色2表示1班在680 <=zf范围有2名学生 
“计”只是本段人数，“累”是由本段向上，不封顶，“其余”段0 <zf <300,总计=计1+计2+..计n 
有n个班就从计1到计n，“累”也是同理 
因为班数不定，希望做成能修改的动态SQL,

if object_id('tb') is not null
drop table tb
go

create table tb([编号] int,[班级] nvarchar(10),[成绩] float) 
insert tb 
select 1,'高三1班',701 union 
select 2,'高三1班',702 union 
select 3,'高三2班',675 union 
select 4,'高三2班',501 union 
select 5,'高三2班',401 union 
select 6,'高三1班',459 union
select 1,'高三2班',700 union 
select 2,'高三1班',690 union 
select 3,'高三1班',681 union 
select 4,'高三1班',671 union 
select 5,'高三2班',450 union 
select 6,'高三1班',209

IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
    DROP FUNCTION f_fsd
go

create function f_fsd(@a int) 
returns varchar(4)
as
begin
  declare @s varchar(4)
  if (@a>0) and (@a <300) 
    set @s='其余'
  else
    set @s=convert(varchar(3),((@a/10)*10))
  return @s
end
go

declare @s nvarchar(4000)
set @s=''
;with t as
(
select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数] 
from tb
group by dbo.f_fsd([成绩]),[班级]
)
select @s=@s+',['+班级+'人数]=sum(case when [班级]='+quotename([班级],'''')+' then [人数] else 0 end),'+
  '['+班级+'人数累计]=isnull((select sum([人数]) from t'+
  ' where (([分数段]>=t1.[分数段] and [分数段]<>''其余'')or(t1.[分数段]=''其余''))'+
  ' and [班级]='+
   quotename([班级],'''')+'),0)'
from t group by [班级]

declare @i int
declare @s2 varchar(1200)
set @s2=''
set @i=700
while @i>290
begin
  set @s2=@s2+' union select '''+convert(char(3),@i)+''',''dump'',0'
  set @i=@i-10
end;
set @s2=@s2+' union select ''其余'',''dump'',0'

exec(
'with t as'+
'('+
'  select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数]'+
'  from tb'+
'  group by dbo.f_fsd([成绩]),[班级]'+
@s2+
') '+
' select [分数段]'+@s+
' ,[总计]=(select sum([人数]) from t where [分数段]=t1.[分数段])'+
' ,[总累计]='+
' case when [分数段]=''其余'' then (select sum([人数]) from t) else '+
'(select sum([人数]) from t where [分数段]>=t1.[分数段] and [分数段]<>''其余'') end'+
' from t t1 group by [分数段]'+
' order by (case when [分数段]=''其余'' then 1 else 0 end), [分数段] desc')

--drop table tb
--drop function f_fsd