按案例陈述中的项目排序

Question

还有其他方法可以适当地按年龄组对结果进行排序吗？作为一项工作，我在每个年龄组之前放置了一个字符，按时间顺序显示，但如果字母不在那里，那么它就不会按照我期望的顺序显示。下面是：

WITH AgeData
AS ( SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - CASE WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
THEN 1
ELSE 0
END AS [Age]
FROM   dbo.Customers ) ,
 GroupAge
AS ( SELECT [Age] ,
        CASE WHEN AGE < 4 THEN 'a0 - 3'
             WHEN AGE BETWEEN 4 AND 8 THEN 'b4 - 8'
             WHEN AGE BETWEEN 9 AND 12 THEN 'c9 - 12'
             WHEN AGE BETWEEN 13 AND 17 THEN 'd13 - 17'
             WHEN AGE BETWEEN 18 AND 22 THEN 'e18 - 22'
             WHEN AGE BETWEEN 23 AND 26 THEN 'f23 - 26'
             WHEN AGE BETWEEN 27 AND 33 THEN 'g27 - 33'
             WHEN AGE BETWEEN 34 AND 40 THEN 'h34 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN 'i41 - 50'
             WHEN AGE BETWEEN 51 AND 60 THEN 'j51 - 60'
             WHEN AGE BETWEEN 61 AND 65 THEN 'k61 - 65'
             WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
             WHEN AGE > 75 THEN 'm75+'
             ELSE 'nInvalid Birthdate'
        END AS [AgeGroups]
 FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
     [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups]
ORDER BY GroupAge.[AgeGroups];

没有'a'，'b'，'c‘等字符.我的结果集看起来如下：

​

​

如果可能的话，我想在不使用字母的情况下正确排序。

Answer 1

可以在订单项中再添加一列。

WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) ,
GroupAge
AS (       SELECT [Age] ,
      CASE WHEN AGE < 4 THEN '0 - 3'
          WHEN AGE BETWEEN 4 AND 8 THEN '4 - 8'
          WHEN AGE BETWEEN 9 AND 12 THEN '9 - 12'
          WHEN AGE BETWEEN 13 AND 17 THEN '13 - 17'
          WHEN AGE BETWEEN 18 AND 22 THEN '18 - 22'
          WHEN AGE BETWEEN 23 AND 26 THEN '23 - 26'
          WHEN AGE BETWEEN 27 AND 33 THEN '27 - 33'
          WHEN AGE BETWEEN 34 AND 40 THEN '34 - 40'
          WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
          WHEN AGE BETWEEN 51 AND 60 THEN '51 - 60'
          WHEN AGE BETWEEN 61 AND 65 THEN '61 - 65'
          WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
          WHEN AGE > 75 THEN 'm75+'
          ELSE 'nInvalid Birthdate'
      END AS [AgeGroups],
      CASE WHEN AGE < 4 THEN 1
          WHEN AGE BETWEEN 4 AND 8 THEN 2
          WHEN AGE BETWEEN 9 AND 12 THEN 3
          WHEN AGE BETWEEN 13 AND 17 THEN 4
          WHEN AGE BETWEEN 18 AND 22 THEN 5
          WHEN AGE BETWEEN 23 AND 26 THEN 6
          WHEN AGE BETWEEN 27 AND 33 THEN 7
          WHEN AGE BETWEEN 34 AND 40 THEN 8
          WHEN AGE BETWEEN 41 AND 50 THEN 9
          WHEN AGE BETWEEN 51 AND 60 THEN 10
          WHEN AGE BETWEEN 61 AND 65 THEN 11
          WHEN AGE BETWEEN 66 AND 74 THEN 12
          WHEN AGE > 75 THEN 13
          ELSE 14
      END AS [AgeGroupId]
FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
      [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups],[AgeGroupId]
ORDER BY GroupAge.[AgeGroupId]

另一种解决方案:使用包含组信息的临时表。

DECLARE @GroupAge AS TABLE
(
   GroupID int,
   StartAge int,
   EndAge int,
   GroupName AS CONCAT(StartAge, '-', EndAge)
)

INSERT INTO @GroupAge
(
    GroupID,
    StartAge,
    EndAge
)
VALUES (1,0,3) -- insert all groups you need

;WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) 
SELECT   COUNT(*) AS [AgeGroupCount] ,
         ga.GroupName
FROM     AgeData a
INNER JOIN @GroupAge ga ON ( a.Age BETWEEN ga.StartAge AND ga.EndAge)
GROUP BY ga.GroupID, ga.GroupName
Order By ga.GroupID

Answer 2

在ORDER BY子句中，只需添加以下一行：

IIF([AgeGroups] = 'Invalid Birthdate', 999, CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT))

这是一个完整的例子：

DECLARE @DataSource TABLE
(
    [AgeGroups] VARCHAR(18)
);

INSERT INTO @DataSource
VALUES ('0-3')
      ,('13-17')
      ,('18-22')
      ,('23-26')
      ,('27-33')
      ,('34-40')
      ,('4-8')
      ,('41-50')
      ,('51-60')
      ,('61-65')
      ,('66-74')
      ,('75+')
      ,('9-12')
      ,('Invalid Birthdate');

SELECT *
      ,IIF([AgeGroups] = 'Invalid Birthdate', 999, CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT))
FROM @DataSource
ORDER BY IIF
         (
            [AgeGroups] = 'Invalid Birthdate'
           ,999
           ,CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT)
         );

​

​

它的想法是得到每个范围的起始编号，将其隐藏为数字并按其排序。我们只需要为Invalid Birthdate字符串添加额外的检查，并将+替换为75+值的-。