在T中按数值分量排序
在T中按数值分量排序
提问于 2014-07-15 10:57:01
这一设想：
create table #scores (score int)
insert into #scores values (1)
insert into #scores values (1)
insert into #scores values (2)
insert into #scores values (3)
insert into #scores values (7)
insert into #scores values (14)
insert into #scores values (14)
;WITH Ranges AS
(
SELECT *
,CASE
WHEN score between 1 and 5
THEN '1-5'
WHEN score between 6 and 10
THEN '6-10'
WHEN score between 11 and 15
THEN '11-15'
END AS ScoreRange
FROM #scores
)
SELECT Count = COUNT(*), ScoreRange
FROM Ranges
GROUP BY ScoreRange
ORDER BY ScoreRange这一数据的结果：
Count ScoreRange
2 11-15
4 1-9
1 6-10是否有一种简单的方法可以将数据按ScoreRange排序，就好像它是数字而不是字符串一样？首先是1-5，然后是6-10，然后是11-15，等等？
回答 5
Stack Overflow用户
回答已采纳
发布于 2014-07-15 11:05:49
好呀
ORDER BY CAST(SUBSTRING(ScoreRange, 0, charindex('-', ScoreRange, 0)) AS INT)
尝尝这个
;WITH Ranges AS
(
SELECT *
,CASE
WHEN score between 1 and 5
THEN '1-5'
WHEN score between 6 and 10
THEN '6-10'
WHEN score between 11 and 15
THEN '11-15'
END AS ScoreRange
FROM #scores
)
SELECT Count = COUNT(*), ScoreRange
FROM Ranges
GROUP BY ScoreRange
ORDER BY CAST(SUBSTRING(ScoreRange, 0, charindex('-', ScoreRange, 0)) AS INT)小提琴演示
Stack Overflow用户
发布于 2014-07-15 11:01:13
在这种情况下我就是这么做的。当您想要以您想要的方式输出数据时，我个人使用它来输出报表或没有任何排序能力的网格。
;WITH Ranges AS
(
SELECT *
,CASE
WHEN score between 1 and 5
THEN '1-5'
WHEN score between 6 and 10
THEN '6-10'
WHEN score between 11 and 15
THEN '11-15'
END AS ScoreRange
FROM #scores
)
SELECT Count = COUNT(*), ScoreRange
FROM Ranges
GROUP BY ScoreRange
ORDER BY LEN(ScoreRange),
ScoreRangeStack Overflow用户
发布于 2014-07-15 11:21:10
最简单的方法就是：
ORDER BY MIN(score)也就是说，从范围和顺序中选择一个任意的分数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24756411
复制相关文章
相似问题
腾讯云开发者
