日期:2014-05-17 浏览次数:20659 次
select score/5 as fs,count(*) fc
from t
group by score/5
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
*/
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<