create table tbscore
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
--------------------------------------------------------------------------------------------
分数段 社会 语文 数学 科学 英语
0-29 0 0 0 0 0
30-39 0 1 1 0 0
40-49 0 1 1 0 0
50-59 0 0 0 0 0
60-69 0 0 0 0 0
70-79 0 0 0 0 0
80-89 0 0 0 0 0
90-99 0 8 8 0 0
100-109 0 0 0 0 0
110-119 0 0 0 0 0
120-129 0 0 0 0 0
130-139 以下全是0
140-149
150-159
160-169
170-179
180-189
190-200
合计 0 10 10 0 0
-------------------------------------------------------------
↑
笔算正确结果 每个分数段都是算所有学校的学生加在一起，高手帮忙。分数段是可变的，我传过来一个参数，如果参数=5，就是从0分到200分以5分为一段，如果参数等于10，就是从0分到200分以10分为一段。
(
select CAST(score - score%@interval AS VARCHAR(10)) + '-' +
CAST((score - score%@interval) + (@interval -1) AS VARCHAR(10)) as d ,coursename,score
from tbscore
)select d,count(case when coursename = '语文' then d end) as 语文,
count(case when coursename = '语文' then d end) as 数学,
count(case when coursename = '外语' then d end) as 外语
from tb
group by d
http://topic.csdn.net/u/20100624/11/270ABAC2-1DAF-4CCC-988C-E14AC3CD8EED.html
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'
declare @i int
set @i = 5 -- 输入参数
select
a.irange,
isnull(b.社会,0) 社会,
isnull(b.语文,0) 语文,
isnull(b.数学,0) 数学,
isnull(b.科学,0) 科学,
isnull(b.英语,0) 英语
from
(
select number * @i ibegin,(number +1)*@i-1 iend,
cast(number * @i as varchar(5)) + '~' + cast((number +1)*@i-1 as varchar(5)) irange
from master..spt_values a
where a.type = 'p' and number < 200/@i) a
left join
(
select
score - score%@i ibegin,
sum(case coursename when '社会' then 1 else 0 end) 社会,
sum(case coursename when '语文' then 1 else 0 end) 语文,
sum(case coursename when '数学' then 1 else 0 end) 数学,
sum(case coursename when '科学' then 1 else 0 end) 科学,
sum(case coursename when '英语' then 1 else 0 end) 英语
from tbscore group by score - score%@i
) b
on a.ibegin = b.ibegin-- 输入5的结果
/*
irange 社会 语文 数学 科学 英语
----------- ----------- ----------- ----------- ----------- -----------
0~4 0 0 0 0 0
5~9 0 0 0 0 0
10~14 0 0 0 0 0
15~19 0 0 0 0 0
20~24 0 0 0 0 0
25~29 0 0 0 0 0
30~34 0 1 1 0 0
35~39 0 0 0 0 0
40~44 0 1 1 0 0
45~49 0 0 0 0 0
50~54 0 0 0 0 0
55~59 0 0 0 0 0
60~64 0 0 0 0 0
65~69 0 0 0 0 0
70~74 0 0 0 0 0
75~79 0 0 0 0 0
80~84 0 0 0 0 0
85~89 0 0 0 0 0
90~94 0 0 0 0 0
95~99 0 8 8 0 0
100~104 0 0 0 0 0
105~109 0 0 0 0 0
110~114 0 0 0 0 0
115~119 0 0 0 0 0
120~124 0 0 0 0 0
125~129 0 0 0 0 0
130~134 0 0 0 0 0
135~139 0 0 0 0 0
140~144 0 0 0 0 0
145~149 0 0 0 0 0
150~154 0 0 0 0 0
155~159 0 0 0 0 0
160~164 0 0 0 0 0
165~169 0 0 0 0 0
170~174 0 0 0 0 0
175~179 0 0 0 0 0
180~184 0 0 0 0 0
185~189 0 0 0 0 0
190~194 0 0 0 0 0
195~199 0 0 0 0 0(40 行受影响)
*/--输入10 的结果
/*
irange 社会 语文 数学 科学 英语
----------- ----------- ----------- ----------- ----------- -----------
0~9 0 0 0 0 0
10~19 0 0 0 0 0
20~29 0 0 0 0 0
30~39 0 1 1 0 0
40~49 0 1 1 0 0
50~59 0 0 0 0 0
60~69 0 0 0 0 0
70~79 0 0 0 0 0
80~89 0 0 0 0 0
90~99 0 8 8 0 0
100~109 0 0 0 0 0
110~119 0 0 0 0 0
120~129 0 0 0 0 0
130~139 0 0 0 0 0
140~149 0 0 0 0 0
150~159 0 0 0 0 0
160~169 0 0 0 0 0
170~179 0 0 0 0 0
180~189 0 0 0 0 0
190~199 0 0 0 0 0(20 行受影响)
*/
set @s=10
select [分数段]=ltrim(number1)+'-'+ltrim(number2) ,
[社会]=sum([社会]),
[语文]=sum([语文]),
[数学]=sum([数学]),
[科学]=sum([科学]),
[英语]=sum([英语])
from
(
select number,
[社会]=sum(case when coursename='社会' then 1 else 0 end),
[语文]=sum(case when coursename='语文' then 1 else 0 end),
[数学]=sum(case when coursename='数学' then 1 else 0 end),
[科学]=sum(case when coursename='科学' then 1 else 0 end),
[英语]=sum(case when coursename='英语' then 1 else 0 end)
from master..spt_values s
left join tbscore b on score=number
where type='p' and number between 0 and 200
group by number
) a,
(
select number1=number,number2=number+@s-1 from master..spt_values s where type='p' and number between 0 and 200 and number%@s=0
)b
where number between number1 and number1
group by ltrim(number1)+'-'+ltrim(number2)
order by cast(left(ltrim(number1)+'-'+ltrim(number2),charindex('-',ltrim(number1)+'-'+ltrim(number2))-1) as int)
分数段 社会 语文 数学 科学 英语
------------------------- ----------- ----------- ----------- ----------- -----------
0-9 0 0 0 0 0
10-19 0 0 0 0 0
20-29 0 0 0 0 0
30-39 0 0 1 0 0
40-49 0 0 1 0 0
50-59 0 0 0 0 0
60-69 0 0 0 0 0
70-79 0 0 0 0 0
80-89 0 0 0 0 0
90-99 0 0 0 0 0
100-109 0 0 0 0 0
110-119 0 0 0 0 0
120-129 0 0 0 0 0
130-139 0 0 0 0 0
140-149 0 0 0 0 0
150-159 0 0 0 0 0
160-169 0 0 0 0 0
170-179 0 0 0 0 0
180-189 0 0 0 0 0
190-199 0 0 0 0 0
200-209 0 0 0 0 0(21 行受影响)
太复杂了，楼主可以无视我
declare @s int
set @s=10select [分数段]=ltrim(number1)+'-'+ltrim(number2) ,
[社会]=sum([社会]),
[语文]=sum([语文]),
[数学]=sum([数学]),
[科学]=sum([科学]),
[英语]=sum([英语])
from
(
select number,
[社会]=sum(case when coursename='社会' then 1 else 0 end),
[语文]=sum(case when coursename='语文' then 1 else 0 end),
[数学]=sum(case when coursename='数学' then 1 else 0 end),
[科学]=sum(case when coursename='科学' then 1 else 0 end),
[英语]=sum(case when coursename='英语' then 1 else 0 end)
from master..spt_values s
left join tbscore b on score=number
where type='p' and number between 0 and 200
group by number
) a
,
(
select number1=number,number2=number+@s-1 from master..spt_values s where type='p' and number between 0 and 200 and number%@s=0
)b
where number between number1 and number2
group by ltrim(number1)+'-'+ltrim(number2)
order by cast(left(ltrim(number1)+'-'+ltrim(number2),charindex('-',ltrim(number1)+'-'+ltrim(number2))-1) as int)分数段 社会 语文 数学 科学 英语
------------------------- ----------- ----------- ----------- ----------- -----------
0-9 0 0 0 0 0
10-19 0 0 0 0 0
20-29 0 0 0 0 0
30-39 0 1 1 0 0
40-49 0 1 1 0 0
50-59 0 0 0 0 0
60-69 0 0 0 0 0
70-79 0 0 0 0 0
80-89 0 0 0 0 0
90-99 0 8 8 0 0
100-109 0 0 0 0 0
110-119 0 0 0 0 0
120-129 0 0 0 0 0
130-139 0 0 0 0 0
140-149 0 0 0 0 0
150-159 0 0 0 0 0
160-169 0 0 0 0 0
170-179 0 0 0 0 0
180-189 0 0 0 0 0
190-199 0 0 0 0 0
200-209 0 0 0 0 0(21 行受影响)
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'create table #tmp (id int identity,col varchar(10))
declare @i int,@k int,@s varchar(8000)
select @i=10,@k=0,@s=isnull(@s+',','')+'['++coursename+']' from tbscore group by coursename
while @k<200
begin
insert into #tmp select ltrim(@k+1)+'~'+ltrim(@k+@i)
set @k=@k+@i
endset @s='
select col,'+@s+' from (
select a.coursename,b.col,1 num,id from (
select coursename,ltrim(score/'+ltrim(@i)+'*'+ltrim(@i)+'+1)+''~''+ltrim(score/'+ltrim(@i)+'*'+ltrim(@i)+'+'+ltrim(@i)+') 分数段 from tbscore
) a full outer join #tmp b on a.分数段=b.col
) a pivot(count(num) for coursename in('+@s+'))b order by id'
print @s
exec(@s)
drop table #tmp
/*
col 数学 语文
1~10 0 0
11~20 0 0
21~30 0 0
31~40 1 1
41~50 1 1
51~60 0 0
61~70 0 0
71~80 0 0
81~90 0 0
91~100 8 8
101~110 0 0
111~120 0 0
121~130 0 0
131~140 0 0
141~150 0 0
151~160 0 0
161~170 0 0
171~180 0 0
181~190 0 0
191~200 0 0
*/
(
schoolname varchar(20) ,
stuname varchar(20),
coursename varchar(10),
score int,
testname varchar(60),
termname varchar(10)
)insert into tbscore
select '金华四中','s1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','数学',30,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华四中','s5','语文',32,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','数学',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','数学',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','数学',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','数学',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','数学',40,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w1','语文',99,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w2','语文',98,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w3','语文',97,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w4','语文',96,'2009年6月份婺城区初一年级各学校摸底考试','初一' union
select '金华五中','w5','语文',42,'2009年6月份婺城区初一年级各学校摸底考试','初一'--科目名动态
--带合计
--分数间隔可变
create table #tmp (id int identity,col varchar(10))
declare @i int,@k int,@s varchar(8000)
select @i=5,@k=0,@s=isnull(@s+',','')+'SUM(CASE WHEN coursename='''+coursename+''' THEN 1 ELSE 0 END) '+coursename from tbscore group by coursename
while @k<200
begin
insert into #tmp select ltrim(@k+1)+'~'+ltrim(@k+@i)
set @k=@k+@i
end
SET @S='
SELECT
CASE WHEN GROUPING(col)=1 THEN ''合计'' ELSE col END ''分数段'','+@s+'
from(
select a.coursename,b.col,1 num,id from (
select coursename,ltrim(score/'+ltrim(@i)+'*'+ltrim(@i)+'+1)+''~''+ltrim(score/'+ltrim(@i)+'*'+ltrim(@i)+'+'+ltrim(@i)+') 分数段 from tbscore
) a full outer join #tmp b on a.分数段=b.col
)a where col is not null group by col WITH ROLLUP order by max(id)
'
PRINT(@S)
EXEC(@S)
drop table #tmp
------------------------------结果-----------------------------
/*
间隔为10
分数段 数学 语文
1~10 0 0
11~20 0 0
21~30 0 0
31~40 1 1
41~50 1 1
51~60 0 0
61~70 0 0
71~80 0 0
81~90 0 0
91~100 8 8
101~110 0 0
111~120 0 0
121~130 0 0
131~140 0 0
141~150 0 0
151~160 0 0
161~170 0 0
171~180 0 0
181~190 0 0
191~200 0 0
合计 10 10间隔为5：
分数段 数学 语文
1~5 0 0
6~10 0 0
11~15 0 0
16~20 0 0
21~25 0 0
26~30 0 0
31~35 1 1
36~40 0 0
41~45 1 1
46~50 0 0
51~55 0 0
56~60 0 0
61~65 0 0
66~70 0 0
71~75 0 0
76~80 0 0
81~85 0 0
86~90 0 0
91~95 0 0
96~100 8 8
101~105 0 0
106~110 0 0
111~115 0 0
116~120 0 0
121~125 0 0
126~130 0 0
131~135 0 0
136~140 0 0
141~145 0 0
146~150 0 0
151~155 0 0
156~160 0 0
161~165 0 0
166~170 0 0
171~175 0 0
176~180 0 0
181~185 0 0
186~190 0 0
191~195 0 0
196~200 0 0
合计 10 10
*/