如何在SSMS中按日历顺序生成并排列年月序列

问题的核心在于：你之前尝试直接对格式化后的年月字符串（比如Jan 15）排序，而字符串排序是按字母顺序（ASCII码）来的，不是实际的日历时间顺序。要解决这个问题，我们需要基于原始的年份和月份的时间属性来构建排序规则，而不是依赖格式化后的文本。

方案一：直接在ORDER BY中使用时间维度的计算值 #

我们可以先把英文月份名称转换成对应的数字，再结合年份生成一个连续的排序数值（比如年份*12 + 月份数），用这个数值来排序就能严格遵循日历顺序。

修改后的完整SQL如下：

SELECT 
    CASE 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'January' then 'Dec ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2001) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'February' then 'Jan ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'March' then 'Feb ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'April' then 'Mar ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'May' then 'Apr ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'June' then 'May ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'July' then 'June ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'August' then 'July ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'September' then 'Aug ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'October' then 'Sep ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'November' then 'Oct ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        WHEN dbo.X_HIST_VASCULAR.MONTH = 'December' then 'Nov ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
    END AS FormattedMonth
FROM dbo.X_HIST_VASCULAR
ORDER BY 
    -- 基于原始年份和转换后的月份数字生成排序键
    dbo.X_HIST_VASCULAR.YEAR * 12 +
    CASE dbo.X_HIST_VASCULAR.MONTH
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'May' THEN 5
        WHEN 'June' THEN 6
        WHEN 'July' THEN 7
        WHEN 'August' THEN 8
        WHEN 'September' THEN 9
        WHEN 'October' THEN 10
        WHEN 'November' THEN 11
        WHEN 'December' THEN 12
    END

方案二：去重后的日历顺序结果 #

如果你需要对结果去重（比如你提到的select distinct month场景），可以用子查询先生成排序字段，再在外层去重并排序：

SELECT DISTINCT
    FormattedMonth
FROM (
    SELECT 
        CASE 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'January' then 'Dec ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2001) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'February' then 'Jan ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'March' then 'Feb ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'April' then 'Mar ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'May' then 'Apr ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'June' then 'May ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'July' then 'June ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'August' then 'July ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'September' then 'Aug ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'October' then 'Sep ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'November' then 'Oct ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
            WHEN dbo.X_HIST_VASCULAR.MONTH = 'December' then 'Nov ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000) 
        END AS FormattedMonth,
        -- 生成用于排序的数值字段
        dbo.X_HIST_VASCULAR.YEAR * 12 +
        CASE dbo.X_HIST_VASCULAR.MONTH
            WHEN 'January' THEN 1
            WHEN 'February' THEN 2
            WHEN 'March' THEN 3
            WHEN 'April' THEN 4
            WHEN 'May' THEN 5
            WHEN 'June' THEN 6
            WHEN 'July' THEN 7
            WHEN 'August' THEN 8
            WHEN 'September' THEN 9
            WHEN 'October' THEN 10
            WHEN 'November' THEN 11
            WHEN 'December' THEN 12
        END AS SortOrder
    FROM dbo.X_HIST_VASCULAR
) AS MonthData
ORDER BY SortOrder

为什么之前的排序不对？ #

字符串排序是按字符的ASCII码顺序来的，比如April的首字母A在ASCII码里比Jan的J靠前，所以ASC排序会先出现April，这完全不符合我们需要的时间顺序。只有基于时间维度的数值（年份+月份数字）排序，才能得到正确的日历序列。

内容的提问来源于stack exchange，提问作者Gee3511