求tsql: 查询出总分分数段

Question

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, 

Answer 1

10一个step貌似动态SQL要溢出,
LZ好像上次发过这个SQL.

Answer 2

if object_id('tb') is not null
drop table tb
gocreate table tb([编号] int,[班级] nvarchar(10),[成绩] float) 
insert tb 
select 1,'高三班',701 union 
select 2,'高三班',702 union 
select 3,'高三班',675 union 
select 4,'高三班',501 union 
select 5,'高三班',401 union 
select 6,'高三班',459 union
select 1,'高三班',700 union 
select 2,'高三班',690 union 
select 3,'高三班',681 union 
select 4,'高三班',671 union 
select 5,'高三班',450 union 
select 6,'高三班',509IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
DROP FUNCTION f_fsd
gocreate function f_fsd(@a int) 
returns int
as
begin
  return ((@a/10)*10)
end
godeclare @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 [班级]='+
   quotename([班级],'''')+'),0)'
from t t1 group by [班级]
exec(
'with t as'+
'('+
'  select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数]'+
'  from tb'+
'  group by dbo.f_fsd([成绩]),[班级]'+
') '+
' select [分数段]'+@s+
' ,[总计]=(select sum([人数]) from t where [分数段]=t1.[分数段])'+
' ,[总累计]=(select sum([人数]) from t where [分数段]>=t1.[分数段])'+
' from t t1 group by [分数段],[班级] order by [分数段] desc')drop table tb
drop function f_fsd
/*
分数段 高三1班人数 高三1班人数累计 高三2班人数 高三2班人数累计 总计 总累计
700 3 3 0 0 3 3
690 1 4 0 0 1 4
680 1 5 0 0 1 5
670 0 5 2 2 2 7
500 2 7 0 2 2 9
450 0 7 2 4 2 11
400 0 7 1 5 1 12
*/

Answer 3

if object_id('tb') is not null
drop table tb
gocreate 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班',509IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
DROP FUNCTION f_fsd
gocreate function f_fsd(@a int) 
returns int
as
begin
  return ((@a/10)*10)
end
godeclare @s nvarchar(4000)
set @s=''
;with t as
(
select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数] 
from tb
group by dbo.f_fsd([成绩]),[班级]
union select 700,'dump',0
union select 690,'dump',0
union select 680,'dump',0
union select 670,'dump',0
union select 660,'dump',0
union select 650,'dump',0
union select 640,'dump',0
union select 630,'dump',0
union select 620,'dump',0
union select 610,'dump',0
union select 600,'dump',0
union select 590,'dump',0
union select 580,'dump',0
union select 570,'dump',0
union select 560,'dump',0
union select 550,'dump',0
union select 540,'dump',0
union select 530,'dump',0
union select 520,'dump',0
union select 510,'dump',0
union select 500,'dump',0
union select 490,'dump',0
union select 480,'dump',0
union select 470,'dump',0
union select 460,'dump',0
union select 450,'dump',0
union select 440,'dump',0
union select 430,'dump',0
union select 420,'dump',0
union select 410,'dump',0
union select 400,'dump',0
)
select @s=@s+',['+班级+'人数]=sum(case when [班级]='+quotename([班级],'''')+' then [人数] else 0 end),'+
  '['+班级+'人数累计]=isnull((select sum([人数]) from t where [分数段]>=t1.[分数段] and [班级]='+
   quotename([班级],'''')+'),0)'
from t
where [班级]<>'dump' 
group by [班级]print @s
exec(
'with t as'+
'('+
'  select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数]'+
'  from tb'+
'  group by dbo.f_fsd([成绩]),[班级]'+
' union select 700,''dump'',0'+
' union select 690,''dump'',0'+
' union select 680,''dump'',0'+
' union select 670,''dump'',0'+
' union select 660,''dump'',0'+
' union select 650,''dump'',0'+
' union select 640,''dump'',0'+
' union select 630,''dump'',0'+
' union select 620,''dump'',0'+
' union select 610,''dump'',0'+
' union select 600,''dump'',0'+
' union select 590,''dump'',0'+
' union select 580,''dump'',0'+
' union select 570,''dump'',0'+
' union select 560,''dump'',0'+
' union select 550,''dump'',0'+
' union select 540,''dump'',0'+
' union select 530,''dump'',0'+
' union select 520,''dump'',0'+
' union select 510,''dump'',0'+
' union select 500,''dump'',0'+
' union select 490,''dump'',0'+
' union select 480,''dump'',0'+
' union select 470,''dump'',0'+
' union select 460,''dump'',0'+
' union select 450,''dump'',0'+
' union select 440,''dump'',0'+
' union select 430,''dump'',0'+
' union select 420,''dump'',0'+
' union select 410,''dump'',0'+
' union select 400,''dump'',0'+
') '+
' select [分数段]'+@s+
' ,[总计]=(select sum([人数]) from t where [分数段]=t1.[分数段])'+
' ,[总累计]=(select sum([人数]) from t where [分数段]>=t1.[分数段])'+
' from t t1 group by [分数段] order by [分数段] desc')drop table tb
drop function f_fsd

Answer 4

if object_id('tb') is not null
drop table tb
gocreate 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班',509IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
DROP FUNCTION f_fsd
gocreate function f_fsd(@a int) 
returns int
as
begin
  return ((@a/10)*10)
end
godeclare @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 [班级]='+
   quotename([班级],'''')+'),0)'
from t group by [班级]exec(
'with t as'+
'('+
'  select dbo.f_fsd([成绩]) as [分数段],[班级], count(*) as [人数]'+
'  from tb'+
'  group by dbo.f_fsd([成绩]),[班级]'+
' union select 700,''dump'',0'+
' union select 690,''dump'',0'+
' union select 680,''dump'',0'+
' union select 670,''dump'',0'+
' union select 660,''dump'',0'+
' union select 650,''dump'',0'+
' union select 640,''dump'',0'+
' union select 630,''dump'',0'+
' union select 620,''dump'',0'+
' union select 610,''dump'',0'+
' union select 600,''dump'',0'+
' union select 590,''dump'',0'+
' union select 580,''dump'',0'+
' union select 570,''dump'',0'+
' union select 560,''dump'',0'+
' union select 550,''dump'',0'+
' union select 540,''dump'',0'+
' union select 530,''dump'',0'+
' union select 520,''dump'',0'+
' union select 510,''dump'',0'+
' union select 500,''dump'',0'+
' union select 490,''dump'',0'+
' union select 480,''dump'',0'+
' union select 470,''dump'',0'+
' union select 460,''dump'',0'+
' union select 450,''dump'',0'+
' union select 440,''dump'',0'+
' union select 430,''dump'',0'+
' union select 420,''dump'',0'+
' union select 410,''dump'',0'+
' union select 400,''dump'',0'+
') '+
' select [分数段]'+@s+
' ,[总计]=(select sum([人数]) from t where [分数段]=t1.[分数段])'+
' ,[总累计]=(select sum([人数]) from t where [分数段]>=t1.[分数段])'+
' from t t1 group by [分数段] order by [分数段] desc')--drop table tb
--drop function f_fsd

Answer 5

谢谢您的回复。
不知能不能把建立sql串的代码用循环简化一下。

Answer 6

if object_id('tb') is not null
drop table tb
gocreate 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班',509IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
    DROP FUNCTION f_fsd
gocreate function f_fsd(@a int) 
returns int
as
begin
  return ((@a/10)*10)
end
godeclare @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 [班级]='+
   quotename([班级],'''')+'),0)'
from t group by [班级]declare @i int
declare @s2 varchar(1000)
set @s2=''
set @i=700
while @i>390
begin
  set @s2=@s2+' union select '+convert(char(3),@i)+',''dump'',0'
  set @i=@i-10
end;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.[分数段])'+
' ,[总累计]=(select sum([人数]) from t where [分数段]>=t1.[分数段])'+
' from t t1 group by [分数段] order by [分数段] desc')--drop table tb
--drop function f_fsd

Answer 7

谢谢您
已经很接近了
各个段都正确计算了，只是“其余”段没有，“其余”的定义：0<成绩<300的人数归入“其余”段（不含0或300）

Answer 8

水晶没有用过，不过润乾，华单和reporting service都是可以实现的
横向扩展就是，group by 班级，分数段
然后填表格的时候，在行上填一个ds.group （班级），列上填ds.group(分数段)这样就可以实现你上面的功能了，前面两个函数是润乾报表里面的函数另外，对于可以对单元格计算的报表工具，直接像 excel那样对单元格写表达式会更加简单。

Answer 9

if object_id('tb') is not null
drop table tb
gocreate 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班',209IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
    DROP FUNCTION f_fsd
gocreate 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
godeclare @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 [班级]='+
   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

Answer 10

不完整:
 
declare @sql  nvarchar(4000)
select @sql= 'select 分数段=(cast(zf/10 as int)*10+10)'
select @sql=@sql+','+quotename('计'+bj)+'=sum(case when bj='+quotename(bj,'''')+ ' then 1 else 0 end)' from (select distinct bj from class)A
 select @sql=@sql+','+quotename('累'+bj)+'=(select count(1) from class B where bj=class.bj and id<=class.id  and bj='+quotename(bj,'''')+')'  from (select distinct bj from class)C
set @sql=@sql+',count(*) 总计 from class group by zf,bj,id order by id'
print @sql
exec (@sql)
分数段         计1          计2          累1          累2          总计          
----------- ----------- ----------- ----------- ----------- ----------- 
710         1           0           1           0           1
710         0           1           0           1           1
690         1           0           2           0           1
510         0           1           0           2           1
410         1           0           3           0           1
110         1           0           4           0           1
710         1           0           5           0           1
610         1           0           6           0           1

Answer 11

类似这样的问题，似乎在EXCEL里面更容易解决。

Answer 12

if object_id('tb') is not null
drop table tb
gocreate 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班',209IF OBJECT_ID('f_fsd', N'FN') IS NOT NULL
    DROP FUNCTION f_fsd
gocreate 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
godeclare @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

Answer 13

请教：
我原来经常去大富翁论坛，近来转到 CSDN。大富翁论坛有一个订阅邮件功能，可以及时收到要回复的邮件。CSDN 不知道有没有订阅邮件功能？否则总是不能及时回复问题。