我想排序给予列升序和降序按月份和年份按字母顺序排列

Question

monthtext <-----(column Name in varchar) 

'2016july'    
'2016september'  
'2016december'   
'2016january'   
'2017february'  
'2017june' 
'2017march'  

我想排序按升序或降序不是按字母顺序。 应该要考虑的月份和年份排序

升序答案应该是像

'2016 january' 
'2016 july' 
'2016 saptember' 
'2016 december' 
'2017 february' 
'2017 march' 
'2017 june' 

Answer 1

你可以使用这个，如果所有的日期是有效的：

SELECT monthtext 
FROM 
    (values 
    ('2016july'),('2016september'),('2016december'),('2016january'), 
    ('2017february'),('2017june'),('2017march')) x(monthtext) 
ORDER BY cast(monthtext as date) 

Answer 2

事情是这样的：

你应该再添个月也case声明

select t.* from (
    select '2016july' as str union all 
    select '2016september' union all 
    select '2016december' union all 
    select '2016january' union all 
    select '2017february' union all 
    select '2017june' union all 
    select '2017march' 
)t 
order by 
    cast(substring(str, 1, 4) as int), 
    case 
     when substring(str, 5, len(str)) = 'january' then 1 
     when substring(str, 5, len(str)) = 'february' then 2 
     when substring(str, 5, len(str)) = 'march' then 3 
     when substring(str, 5, len(str)) = 'june' then 6 
     when substring(str, 5, len(str)) = 'july' then 7 
     when substring(str, 5, len(str)) = 'september' then 9 
     when substring(str, 5, len(str)) = 'december' then 12 
    end 

Answer 3

您需要将其拆分为年份&月然后应用Order By子句。

模式：

CREATE TABLE #TAB (NAME VARCHAR(50)) 

INSERT INTO #TAB 
SELECT '2016july' 
UNION ALL 
SELECT '2016september' 
UNION ALL 
SELECT '2016december' 
UNION ALL 
SELECT '2016january' 
UNION ALL 
SELECT '2017february' 
UNION ALL 
SELECT '2017june' 
UNION ALL 
SELECT '2017march' 

现在不要选择像下面

SELECT NAME 
, CAST(SUBSTRING(NAME, 1, PATINDEX('%[a-z]%',NAME)-1) AS INT) YEAROF 
,DATEPART(MM, SUBSTRING(NAME,PATINDEX('%[a-z]%',NAME),LEN(NAME)) +' 01 2017') MONTHOF 
FROM #TAB 
ORDER BY YEAROF,MONTHOF 

结果：

+---------------+--------+---------+ 
|  NAME  | YEAROF | MONTHOF | 
+---------------+--------+---------+ 
| 2016january | 2016 |  1 | 
| 2016july  | 2016 |  7 | 
| 2016september | 2016 |  9 | 
| 2016december | 2016 |  12 | 
| 2017february | 2017 |  2 | 
| 2017march  | 2017 |  3 | 
| 2017june  | 2017 |  6 | 
+---------------+--------+---------+