按特定顺序选择月份的SQL查询，如从3月到2月、从4月到3月、从11月到10月

Question

我必须编写一个查询来按特定的顺序选择月份，比如从3月到2月，或者从2月到1月。我不能使用存储过程。我必须使用SELECT，在数据库中，月份的名称存储为“一月”、“二月”、“三月”等等……

在获取月份时，我只需要前3个字符。

结果应该是

jan 1
feb 2
mar 3
apr 4
may 5
jun 6
jul 7
aug 8
sep 9
oct 10
nov 11
dec 12

使用此查询后

Select distinct upper(convert(varchar(3),datename(MONTH,InvoiceDate))) as monthName,upper(convert(nvarchar,datepart(mm,InvoiceDate))) as 
    mon  
from OpenInvoiceDetails 
order by mon

我得到了

jan 1
oct 10
nov 11
dec 12
feb 2
mar 3
apr 4
may 5
jun 6
jul 7
aug 8
sep 9

Answer 1

我在这里做了很多假设，但像这样的东西是可行的：

    Create Table Months
(
    DateColumn varchar(20)
)

INSERT INTO Months

SELECT 'January' 
UNION
SELECT 'February'
UNION
SELECT 'March'
UNION
SELECT 'April'
UNION
SELECT'May' 
UNION
SELECT 'June' 
UNION
SELECT 'July' 
UNION
SELECT 'August'
UNION
SELECT 'September'
UNION
 SELECT'October' 
UNION
SELECT 'November'
UNION
SELECT 'December'

SELECT  DateColumn ,( CASE DateColumn
            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 [Month]
From Months
Order by Month

为您提供以下内容：

January 1
February    2
March   3
April   4
May 5
June    6
July    7
August  8
September   9
October 10
November    11
December    12

按月排序说明：

December    12
November    11
October 10
September   9
August  8
July    7
June    6
May 5
April   4
March   3
February    2
January 1

Answer 2

这并不是最优的，但它应该可以解决无效的列名。

Select distinct upper(convert(varchar(3),datename(MONTH,InvoiceDate))) as monthName
,upper(convert(nvarchar,datepart(mm,InvoiceDate))) as mon 
from 
 OpenInvoiceDetails 
order by Len(convert(nvarchar,datepart(mm,InvoiceDate))),
convert(nvarchar,datepart(mm,InvoiceDate))

Answer 3

一种选择是更改您的ORDER BY

ORDER BY Cast(mon as int) 

祝好运。