极度sql统计难题（用一个存储过程来实现）

[comeonstuding]

如：
成绩表
学号 成绩 学校代号
001 730 01
002 630 01
003 722 02
004 725 02
005 730 03

学校表
学校代号 学校
01 一中
02 二中

成绩分布表
分数范围 最小值 最大值
700~750 700 750
650~700 650 700
得到的表是统计成绩分布表
分布范围 一中 二中
700~750 1 2
650~700 0 0

[good2speed]

又是一个交叉表的问题,自己搜搜就明白

[zjcxc]

还差个成绩吧? 不然两个表怎么对应?

[hudan]

select z.分数范围,
sum(case when 学校='一中' then 个数 else 0 end) as 一中,
sum(case when 学校='二中' then 个数 else 0 end) as 二中
from
(
select 分数范围,学校,count(*) as 个数 from
(select 分数范围,c.学校,b.成绩 from 成绩分布表 a ,成绩表 b ,学校表 c
where b.成绩 > a.最小值 and b.成绩<a.最大值 and b.学校代号=c.学校代号) t
group by 分数范围,学校
) z
group by z.分数范围
order by 分数范围 desc

[hudan]

declare @strsum as varchar(2000)
set @strsum=''
select @strsum=@strsum+',sum(case when 学校='''+rtrim(学校)+''' then 个数 else 0 end) as '+rtrim(学校)
from (select distinct 学校 from 学校表 ) z

declare @sql as varchar(8000)
set @sql='select z.分数范围 '+@strsum+
' from
(
select 分数范围,学校,count(*) as 个数 from
(select 分数范围,c.学校,b.成绩 from 成绩分布表 a ,成绩表 b ,学校表 c
where b.成绩 > a.最小值 and b.成绩<a.最大值 and b.学校代号=c.学校代号) t
group by 分数范围,学校
) z
group by z.分数范围
order by 分数范围 desc'

exec(@sql)

[hudan]

应该是这样：

CREATE PROCEDURE dbo.StatScores AS
declare @strsum as varchar(2000)
set @strsum=''
select @strsum=@strsum+',sum(case when 学校代号='''+rtrim(学校代号)+''' then 个数 else 0 end) as '+rtrim(学校)
from (select distinct 学校,学校代号 from 学校表 ) z

declare @sql as varchar(8000)
set @sql='select z.分数范围 '+@strsum+
' from
(
select 分数范围,学校代号,count(*) as 个数 from
(select 分数范围,b.成绩,b.学校代号 from 成绩分布表 a ,成绩表 b
where b.成绩 > a.最小值 and b.成绩<a.最大值) t
group by 分数范围,学校代号
) z
group by z.分数范围
order by 分数范围 desc'

exec(@sql)

GO

[comeonstuding]

学校表
学校代号 学校
01 一中
02 二中

成绩分布表
分数范围 最小值 最大值
700~750 700 750
650~700 650 700

学校代号 学校
01 一中
02 二中

成绩分布表
分数范围 最小值 最大值
700~750 700 750
650~700 650 700
以上两个表是条件只要符合条件的显示，不符合的不统计显示。
但生成的表必须要按照学校表和成绩分布表中的顺序排列
比如
学校代号 学校
02 二中
01 一中

得到的表是统计成绩分布表
分布范围 二中 二中
700~750 2 1
650~700 0 0

[pisces007]

select c.分数范围 ,b.学校代号
into #a from 成绩表 b join 成绩分布表 c
on b.name between c.最小值 and c.最大值

select c.分数范围 ,b.学校代号 into #a
from 成绩表 b join 成绩分布表 c
on b.name between c.最小值 and c.最大值

declare @sql nvarchar(8000)
set @sql='select 分数范围,'
select @sql=@sql +' sum(case 学校代号 when ''' +学校代号 +''' then 1 else 0 end) as ''' +学校 +''',' from 学校表
select @sql=left(@sql,len(@sql)-1) +' from #a group by 分数范围'
exec( @sql)
drop table #a

经测试正确

[pisces007]

上面的b.name改为b.成绩
我的表的字段名和你的不一样，这个没改过来

[zjcxc]

--开始没有看到楼主的成绩表,这样就可以了.

declare @s varchar(8000)
set @s=''
select @s=@s+',['+学校+']=sum(case b.学校代号 when '''+学校代号+''' then 1 else 0 end)'
from 学校表

exec('select a.分数范围'+@s+'
from 成绩分布表 a
left join 成绩表 b on b.成绩 between a.最小值 and a.最大值
group by a.分数范围
order by a.分数范围 desc')

[zjcxc]

--测试

--测试数据

create table 成绩表(学号 varchar(10),成绩 int,学校代号 varchar(10))
insert 成绩表 select '001',730,'01'
union all select '002',630,'01'
union all select '003',722,'02'
union all select '004',725,'02'
union all select '005',730,'03'

create table 学校表(学校代号 varchar(10),学校 varchar(10))
insert 学校表 select '01','一中'
union all select '02','二中'

create table 成绩分布表(分数范围 varchar(10),最小值 int,最大值 int)
insert 成绩分布表 select '700~750',700,750
union all select '650~700',650,700
go

declare @s varchar(8000)
set @s=''
select @s=@s+',['+学校+']=sum(case b.学校代号 when '''+学校代号+''' then 1 else 0 end)'
from 学校表
order by 学校代号

exec('select a.分数范围'+@s+'
from 成绩分布表 a
left join 成绩表 b on b.成绩 between a.最小值 and a.最大值
group by a.分数范围
order by a.分数范围 desc')
go

--删除测试
drop table 学校表,成绩分布表,成绩表

/*--测试结果

分数范围 一中 二中
---------- ----------- -----------
700~750 1 2
650~700 0 0
--*/