一个汇总报表的问题

Question

有两个表:
1.成绩分段表(ID为自增列)
ID  MaxFen  MinFen
--------------------
 1   510     500
 2   500     490
 3   490     4802.总成绩表
ClassID   StudentID  SumFen(总分)
------------------------------------
000005     000001      503
000005     000003      498
000004     000006      487
000004     000003      5013.班级表
ClassID   ClassName
-----------------------
000004     104班
000005     105班要求结果:
         104班      105班
----------------------------
510-500    1          1
500-490               1 
480-490    1

Answer 1

create table ScoreSum(classid varchar(10),studentid varchar(10),sumfen int)
create table ClassTb(classid varchar(10),classname varchar(10))
insert into ScoreSum select '000005','000001',503
union all select '000005'  ,  '000003' ,    498
union all select '000004',    '000006',      487
union all select '000004',     '000003',      501insert into ClassTb select '000004',     '104班'
union all select '000005',     '105班'-- select * from ScoreSum
-- select * from ClassTb
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case classid when '+rtrim(a.classid)+' then 1 else null end) ['+a.classname+']' from (select distinct b.* from scoresum a,classtb b where a.classid=b.classid) a
print @sqlexec('select ''510-500'' [分数段]'+@sql+' from ScoreSum where sumfen>=500 and sumfen<510
union all
select ''500-490'' [分数段]'+@sql+' from ScoreSum where sumfen>=490 and sumfen<500
union all
select ''490-480'' [分数段]'+@sql+' from ScoreSum where sumfen>=480 and sumfen<490
')

Answer 2

--你先测试下
--我这边没有问题
--结果
分数段   104班    105班
510-500 1 1
500-490 NULL 1
490-480 1 NULL

Answer 3

/**建立測試環境**/
create table ScoreRange (id int identity,maxfen int,minfen int)
create table ScoreSum(classid varchar(10),studentid varchar(10),sumfen int)
create table ClassTb(classid varchar(10),classname varchar(10))insert into ScoreRange (maxfen,minfen)
            select 510,500 
 union all select 500,490 
union all select  490,480insert into ScoreSum select '000005','000001',503
union all select '000005'  ,  '000003' ,    498
union all select '000004',    '000006',      487
union all select '000004',     '000003',      501insert into ClassTb select '000004',     '104班'
union all select '000005',     '105班'/**建個臨時表temp_t,方便表述**/
select  *  into temp_t  from  ScoreRange a
 left join (select classname,studentid,sumfen  
                from ScoreSum inner join ClassTb 
              on ScoreSum.classid=ClassTb.classid) b
 on b.sumfen between a.minfen and a.maxfen/**動態行轉列**/
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when classname='''+classname+''' then 1 else 0  end ) as ['+classname+']' from temp_t  group by classname
set @sql='select convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) as 分數段'+@sql+' from temp_t  group by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) order by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) desc '
exec(@sql)/*刪除臨時表*/
drop table temp_t/*刪除測試環境*/
drop table ScoreRange
drop table ScoreSum
drop table ClassTb/*結果*/
/*
分數段       104班        105班        
--------- ----------- ----------- 
510-500   1           1
500-490   0           1
490-480   1           0
*/

Answer 4

借用playwarcraft的表
/**建立測試環境**/
create table ScoreRange (id int identity,maxfen int,minfen int)
create table ScoreSum(classid varchar(10),studentid varchar(10),sumfen int)
create table ClassTb(classid varchar(10),classname varchar(10))insert into ScoreRange (maxfen,minfen)
            select 510,500 
 union all select 500,490 
union all select  490,480insert into ScoreSum select '000005','000001',503
union all select '000005'  ,  '000003' ,    498
union all select '000004',    '000006',      487
union all select '000004',     '000003',      501insert into ClassTb select '000004',     '104班'
union all select '000005',     '105班'declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when classname='''+classname+''' then 1 else 0  end ) as ['+classname+']' 
  from ScoreSum a,ClassTb b WHere a.classid=b.classid Group by classname
set @sql='select convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) as 分数段'+@sql+' from ScoreRange z,ScoreSum a,ClassTb b Where (a.sumfen between z.minfen and z.maxfen) AND a.classid=b.classid  group by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) order by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) desc '
exec(@sql)

Answer 5

使用between 可能会产生重复数据,使用下面的语句来实现:
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when cClassName='''+cClassName+''' then 1 else 0  end ) as ['+cClassName+']' 
  from tempdb..SumDengDi a,EC_Class b WHere a.cClassID=b.cClassID Group by cClassName
set @sql='select convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) as 分数段'+@sql+' from FenDuan z,tempdb..SumDengDi a,EC_Class b Where (a.sumResult>=z.MinFen and a.sumResult<z.MaxFen) AND a.cClassID=b.cClassID  group by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) order by convert(varchar(04),maxfen)+''-''+convert(varchar(04),minfen) desc '
exec(@sql)将a.sumfen between z.minfen and z.maxfen 写成:a.sumResult>=z.MinFen and a.sumResult<z.MaxFen