select
sum(case when 成绩 between 0 and 59 then 1 else 0 end) as [0~59],
sum(case when 成绩 between 60 and 79 then 1 else 0 end) as [60~79],
sum(case when 成绩 between 80 and 89 then 1 else 0 end) as [80~89],
sum(case when 成绩 between 90 and 100 then 1 else 0 end) as [90~100]
from
A
感谢这位仁兄的回答,我没说清楚,成绩范围不能固定死。。
B.范围,
数量=ISNULL((select count(1)
from A
where A.成绩
between cast(left(B.范围,charindex('~',B.范围))) as int) and cast(right(B.范围,len(B.范围)-charindex('~',B.范围))) as int)),0)
from B
如果需求是动态变化的,那么就用动态SQL语句拼接类似1楼所给出形式的SQL语句,变化的内容就是(case when ... end)的那些条件。
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([成绩] int)
insert [A]
select 80 union all
select 90 union all
select 100 union all
select 70 union all
select 60 union all
select 50 union all
select 40
if object_id('[B]') is not null drop table [B]
go
create table [B]([范围] varchar(6))
insert [B]
select '0~60' union all
select '60~80' union all
select '80~90' union all
select '90~100'
---查询---
select
B.范围,
数量=ISNULL((select count(1)
from A
where A.成绩
between cast(left(B.范围,charindex('~',B.范围)-1) as int) and cast(right(B.范围,len(B.范围)-charindex('~',B.范围)) as int)),0)
from B---结果---
范围 数量
------ -----------
0~60 3
60~80 3
80~90 2
90~100 2(所影响的行数为 4 行)
--生成测试数据
create table tabname(id int identity(1,1),score int)
insert into tabname(score) select 20
union all select 100
union all select 47
union all select 93
union all select 16
union all select 36
union all select 64
union all select 81
union all select 20
union all select 21
union all select 44
union all select 96
union all select 61
union all select 50
union all select 41
union all select 84
union all select 29
union all select 76
union all select 90
union all select 61
union all select 24
union all select 89
union all select 56
union all select 25
union all select 96
union all select 69
union all select 44
go--创建处理动态取值的存储过程
create procedure sp_test(@str varchar(50))
as
begin
declare @sql varchar(8000),@var varchar(20)
select @str=@str+',',@sql=''
while charindex(',',@str)>0
begin
select @var=left(@str,charindex(',',@str)-1),
@str=stuff(@str,1,charindex(',',@str),'')
set @sql=@sql+',['+@var+']=sum(case when score between '
+left(@var,charindex('~',@var)-1)+' and '+stuff(@var,1,charindex('~',@var),'')+' then 1 else 0 end)'
end
set @sql='select '+stuff(@sql,1,1,'')+' from tabname'
--print @sql
exec(@sql)
end
go--调用存储过程,存储过程输入格式如下例所示:
exec sp_test '0~60,60~80,80~90,90~100'--查看存储过程执行结果
/*
0~60 60~80 80~90 90~100
----------- ----------- ----------- -----------
14 5 4 5
*/
go--清除测试环境
drop procedure sp_test
drop table tabname
go
from
(select a.score,b.kind
from a,b
where convert(a.score,varchar(2))>=left(b.kind,2) and convert(a.score,varchar(2))<right(b.kind,2)) test
group by kind以上代码或有错误,仅仅是一种参考思路