求高手指点~查询某一个分数段的人数

[NingJuHuan]

查询一个表里面某一分段里面有多少人表1
sno score
1 150
2 133
3 123
4 80
5 90
6 60
7 34
8 21
9 102
表2
sno score
11 12
22 105
33 76
42 98
53 88
63 78
72 77
82 111
92 132
查询score在150,145-140,140-135.......5-0分数段的人，
如果将此分数间隔变成10，20又该怎么查？

[haitao]

select score/5 as fs,count(*) fc
from t
group by score/5

[NingJuHuan]

可能是没描述好，数据量比较大，然后出结果最好是以EXCEL文件保存那种

[haitao]

引用 2 楼 NingJuHuan 的回复:

可能是没描述好，数据量比较大，然后出结果最好是以EXCEL文件保存那种

查询结果可以保存为多种格式的文件的 也可以全选，复制粘贴到excel

[xxfvba]

select ceiling(score/5.0),count(*) from 表1 group by ceiling(score/5.0)

[chen357313771]

没明白，表1表2啥关系，比如：90属于80到90直接还是90到100之间呢 不明白，不明白，是不是你想要的。。。

DECLARE @SpaceScore INT=10  --分数间隔
DECLARE @EndScore	INT=150
IF OBJECT_ID('tempdb..#ScoreOut','U') IS NOT NULL DROP TABLE #ScoreOut
CREATE TABLE #ScoreOut
(
	 Id INT IDENTITY(1,1)
	,StartScore	INT
	,EndScore	INT
)
;WITH CTE(Score)
AS (
	SELECT 0 AS Score
	UNION ALL
	SELECT Score+@SpaceScore
	FROM CTE
	WHERE Score<@EndScore-@SpaceScore
)
INSERT INTO #ScoreOut(StartScore,EndScore)
SELECT Score,Score+@SpaceScore
FROM CTE
IF OBJECT_ID('tempdb..#Score','U') IS NOT NULL DROP TABLE #Score
CREATE TABLE #Score
(
	 sno INT 
	,Score	INT
)
INSERT INTO #Score
SELECT 1,	150
UNION ALL SELECT 2,	133
UNION ALL SELECT 3,	123
UNION ALL SELECT 4,	80
UNION ALL SELECT 5,	90
UNION ALL SELECT 6,	60
UNION ALL SELECT 7,	34
UNION ALL SELECT 8,	21
UNION ALL SELECT 9,	102


SELECT CAST(B.StartScore AS VARCHAR)+'-'+CAST(B.EndScore AS VARCHAR) AS ScoreSpace
	,COUNT(*) AS Cnt
FROM #Score AS A
JOIN #ScoreOut AS B ON A.Score BETWEEN B.StartScore AND B.EndScore
GROUP BY CAST(B.StartScore AS VARCHAR)+'-'+CAST(B.EndScore AS VARCHAR)


/*
ScoreSpace	Cnt
100-110	1
120-130	1
130-140	1
140-150	1
20-30	1
30-40	1
50-60	1
60-70	1
70-80	1
80-90	2
90-100	1
*/

[LongRui888]

你这里的 ： 查询score在150,145-140,140-135.......5-0 具体是怎么判断的呢，比如 140分，是算在：145-140, 还是算在：140-135

[LongRui888]

试试：

if object_id('s') is not null
   drop table s
go

create table s(id int,score int)

INSERT INTO s
SELECT 1,	150
UNION ALL SELECT 2,	133
UNION ALL SELECT 3,	123
UNION ALL SELECT 4,	80
UNION ALL SELECT 5,	90
UNION ALL SELECT 6,	60
UNION ALL SELECT 7,	34
UNION ALL SELECT 8,	21
UNION ALL SELECT 9,	102
go

declare @table_name nvarchar(100);
declare @sql nvarchar(max);
declare @start int;
declare @end int;
declare @interval int;

select @start = 0,    --开始的分数
       @end = 150,    --结束的分数
       @interval = 10,--间隔
       @sql = '',
       @table_name = 's';  --这里的表的名称是s

;with t
as
(
select @start as start_score,@start + @interval as end_score
union all
select start_score + @interval,
       case when end_score + @interval > @end
                 then @end
            else end_score + @interval
       end
from t
where start_score + @interval < @end
),

tt
as
(
select start_score,
       end_score,
       cast(end_score as varchar) +'-'+cast(start_score as varchar) as score_range
from t
)

select @sql = @sql + 
              ',count(case when score >'+cast(start_score as varchar) + 
              ' and score <= '+cast(end_score as varchar) + 
              ' then 1 else null end) as ['+score_range +']'
from tt

set @sql = 'select '+stuff(@sql,1,1,'') + ' from '+@table_name 

--select @sql

exec(@sql)
/*
10-0	20-10	30-20	40-30	50-40	60-50	70-60	80-70	90-80	100-90	110-100	120-110	130-120	140-130	150-140
0	0	1	1	0	1	0	1	1	0	1	0	1	1	1
*/

[LongRui888]

上面的是间隔为10的，只需要修改@interval=10 就可以设置建个为10的：

if object_id('s') is not null
   drop table s
go

create table s(id int,score int)

INSERT INTO s
SELECT 1,	150
UNION ALL SELECT 2,	133
UNION ALL SELECT 3,	123
UNION ALL SELECT 4,	80
UNION ALL SELECT 5,	90
UNION ALL SELECT 6,	60
UNION ALL SELECT 7,	34
UNION ALL SELECT 8,	21
UNION ALL SELECT 9,	102
go

declare @table_name nvarchar(100);
declare @sql nvarchar(max);
declare @start int;
declare @end int;
declare @interval int;

select @start = 0,    --开始的分数
       @end = 150,    --结束的分数
       @interval = 20,--这里的间隔改为20
       @sql = '',
       @table_name = 's';  --这里的表的名称是s

;with t
as
(
select @start as start_score,@start + @interval as end_score
union all
select start_score + @interval,
       case when end_score + @interval > @end
                 then @end
            else end_score + @interval
       end
from t
where start_score + @interval < @end
),

tt
as
(
select start_score,
       end_score,
       cast(end_score as varchar) +'-'+cast(start_score as varchar) as score_range
from t
)

select @sql = @sql + 
              ',count(case when score >'+cast(start_score as varchar) + 
              ' and score <= '+cast(end_score as varchar) + 
              ' then 1 else null end) as ['+score_range +']'
from tt

set @sql = 'select '+stuff(@sql,1,1,'') + ' from '+@table_name 

--select @sql

exec(@sql)
/*
20-0	40-20	60-40	80-60	100-80	120-100	140-120	150-140
0	2	1	1	1	1	2	1
*/

[NingJuHuan]

谢谢~~~~

引用 8 楼 yupeigu 的回复:

上面的是间隔为10的，只需要修改@interval=10 就可以设置建个为10的：

if object_id('s') is not null
   drop table s
go

create table s(id int,score int)

INSERT INTO s
SELECT 1,	150
UNION ALL SELECT 2,	133
UNION ALL SELECT 3,	123
UNION ALL SELECT 4,	80
UNION ALL SELECT 5,	90
UNION ALL SELECT 6,	60
UNION ALL SELECT 7,	34
UNION ALL SELECT 8,	21
UNION ALL SELECT 9,	102
go

declare @table_name nvarchar(100);
declare @sql nvarchar(max);
declare @start int;
declare @end int;
declare @interval int;

select @start = 0,    --开始的分数
       @end = 150,    --结束的分数
       @interval = 20,--这里的间隔改为20
       @sql = '',
       @table_name = 's';  --这里的表的名称是s

;with t
as
(
select @start as start_score,@start + @interval as end_score
union all
select start_score + @interval,
       case when end_score + @interval > @end
                 then @end
            else end_score + @interval
       end
from t
where start_score + @interval < @end
),

tt
as
(
select start_score,
       end_score,
       cast(end_score as varchar) +'-'+cast(start_score as varchar) as score_range
from t
)

select @sql = @sql + 
              ',count(case when score >'+cast(start_score as varchar) + 
              ' and score <= '+cast(end_score as varchar) + 
              ' then 1 else null end) as ['+score_range +']'
from tt

set @sql = 'select '+stuff(@sql,1,1,'') + ' from '+@table_name 

--select @sql

exec(@sql)
/*
20-0	40-20	60-40	80-60	100-80	120-100	140-120	150-140
0	2	1	1	1	1	2	1
*/