按T-SQL中值范围的数字组件排序

Question

此场景：

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，以此类推？

Answer 1

为什么不

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)

FIDDLE演示

Answer 2

这就是我在这种情况下所做的。当你想以你想要的方式输出数据时，这非常有用，我个人将其用于报告输出或不具有任何排序功能的网格。

;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),
         ScoreRange

Answer 3

最简单的方法就是：

ORDER BY MIN(score)

也就是说，从范围和顺序中选择一个任意的分数。

Answer 4

因为ScoreRange是一个字符串，所以我们可以期望字符串/单词排序，而不是数字排序。

在这种排序形式中，数字位于字母和字符之前，因此11位于1-和6-之前，1-位于6-之前。

这给了我们：

11-15
1-5
6-10

如果ScoreRange值改为前缀为零的(1-5 -> 01-05)，则排序将发生变化。

使用相同的字符串/单词排序，01-在05-之前，05-在10-之前。

数字的语义(1在5之前，也就是在10之前(被保留，但这现在在字符串/单词比较下起作用。

这种方法适用于99以下的所有值；如果你的最大值变高，再加一个零：

001-005
006-010
011-015
016-100
101-999

您的查询现在变成：

;WITH Ranges AS
(
     SELECT *,
            CASE 
                 WHEN score between 1 and 5 THEN '01-05'
                 WHEN score between 6 and 10 THEN '06-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

请参阅我的SQL fiddle：http://sqlfiddle.com/#！3/f0699/2/0

Answer 5

试试这个：

;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 CASE ScoreRange 
                WHEN '1-5' THEN 1 
                WHEN '6-10' THEN 2
                ELSE 3
        END