如何创建序数(即“1”“2”等)在SQL Server中

Question

我最近在SSRS-2008标签中响应了to this question，它要求将日期中的天数更改为序号(即"1“、”2“而不是”1“、"2")。该解决方案涉及一个VB.Net函数。我很好奇如何在SQL (特别是task和Server )中执行这个任务，或者是否有一些内置的支持。

下面是一个场景:假设您已经为1000名跑步者组织了一次跑迹，并在一个表中得到了结果，其中列名和位置(以正常数字表示)。您希望创建一个查询，该查询将显示用户名及其在序号中的位置。

Answer 1

这里有一个可伸缩的解决方案，应该适用于任何数字。我以为其他人用% 100表示11,12,13，但我错了。

WITH CTE_Numbers
AS
(
    SELECT 1 num
    UNION ALL
    SELECT num + 1
    FROM CTE_Numbers
    WHERE num < 1000
)

SELECT  CAST(num AS VARCHAR(10))
        +
        CASE
            WHEN num % 100 IN (11,12,13) THEN 'th' --first checks for exception
            WHEN num % 10 = 1 THEN 'st'
            WHEN num % 10 = 2 THEN 'nd'
            WHEN num % 10 = 3 THEN 'rd'
            ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0)
        END
FROM CTE_Numbers
OPTION (MAXRECURSION 0)

Answer 2

您可以像在应用程序层中一样轻松地在SQL中这样做：

DECLARE @myDate DATETIME = '2015-05-21';
DECLARE @day INT;
SELECT  @day = DAY(@myDate);
SELECT  CASE WHEN @day IN ( 11, 12, 13 ) THEN CAST(@day AS VARCHAR(10)) + 'th'
             WHEN @day % 10 = 1 THEN CAST(@day AS VARCHAR(10)) + 'st'
             WHEN @day % 10 = 2 THEN CAST(@day AS VARCHAR(10)) + 'nd'
             WHEN @day % 10 = 3 THEN CAST(@day AS VARCHAR(10)) + 'rd'
             ELSE CAST(@day AS VARCHAR(10)) + 'th'
        END

如果有必要，也可以将其放入标量函数中。

编辑

就你的例子而言，它将是：

SELECT  Name ,
        CASE WHEN Place IN ( 11, 12, 13 )
             THEN CAST(Place AS VARCHAR(10)) + 'th'
             WHEN Place % 10 = 1 THEN CAST(Place AS VARCHAR(10)) + 'st'
             WHEN Place % 10 = 2 THEN CAST(Place AS VARCHAR(10)) + 'nd'
             WHEN Place % 10 = 3 THEN CAST(Place AS VARCHAR(10)) + 'rd'
             ELSE CAST(Place AS VARCHAR(10)) + 'th'
        END AS Place
FROM    FootRaceResults;

Answer 3

非常害怕：

with
  ArabicRomanConversions as (
    select *
      from ( values
        ( 0, '', '', '', '' ), ( 1, 'I', 'X', 'C', 'M' ), ( 2, 'II', 'XX', 'CC', 'MM' ), ( 3, 'III', 'XXX', 'CCC', 'MMM' ), ( 4, 'IV', 'XL', 'CD', '?' ),
        ( 5, 'V', 'L', 'D', '?' ), ( 6, 'VI', 'LX', 'DC', '?' ), ( 7, 'VII', 'LXX', 'DCC', '?' ), ( 8, 'VIII', 'LXXX', 'DCCC', '?' ), ( 9, 'IX', 'XC', 'CM', '?' )
        ) as Placeholder ( Arabic, Ones, Tens, Hundreds, Thousands )
      ),
  OrdinalConversions as (
    select *
      from ( values
        ( 1, 'st' ), ( 2, 'nd' ), ( 3, 'rd' ), ( 11, 'th' ), ( 12, 'th' ), ( 13, 'th' )
        ) as Placeholder2 ( Number, Suffix )
      ),
  Numbers as (
    select 1 as Number
    union all
    select Number + 1
      from Numbers
      where Number < 3999 )
  select Number as Arabic,
    ( select Thousands from ArabicRomanConversions where Arabic = Number / 1000 ) +
    ( select Hundreds from ArabicRomanConversions where Arabic = Number / 100 % 10 ) +
    ( select Tens from ArabicRomanConversions where Arabic = Number / 10 % 10 ) +
    ( select Ones from ArabicRomanConversions where Arabic = Number % 10 ) as Roman,
    Cast( Number as VarChar(4) ) + Coalesce( (
      select top 1 Suffix from OrdinalConversions where Number = Numbers.Number % 100 or Number = Numbers.Number % 10 order by Number desc ), 'th' ) as Ordinal
    from Numbers option ( MaxRecursion 3998 );