如何在sql中使用自定义排序顺序

本文关键字：自定义 排序 顺序 sql | 更新日期: 2023-09-27 18:09:53

我不想按asc或desc对列进行排序，但我希望它根据我设置的顺序进行排序。例如，我有以下数据:

Varchar1 |  Varchar2 | Value | Something
1401_1   | 1401_1_9  | 1     | Something1
1401_1   | 1401_1_9  | 0     | Something2
1401_1   | 1401_1_11 | 1     | Something1
1401_1   | 1401_1_11 | 1     | Something2
1401_1   | 1401_1_13 | 0     | Something1
1401_1   | 1401_1_13 | 1     | Something2
1401_1   | 1401_1_15 | 0     | Something1
1401_1   | 1401_1_15 | 1     | Something2
1401_1   | 1401_1_17 | 1     | Something1
1401_1   | 1401_1_17 | 0     | Something2
1401_1   | 1401_1_19 | 1     | Something1
1401_1   | 1401_1_19 | 0     | Something2
1401_1   | 1401_1_21 | 1     | Something1
1401_1   | 1401_1_21 | 1     | Something2
1401_1   | 1401_1_23 | 0     | Something1
1401_1   | 1401_1_23 | 1     | Something2
1401_1   | 1401_1_1  | 0     | Something1
1401_1   | 1401_1_1  | 1     | Something2
1401_1   | 1401_1_3  | 1     | Something1
1401_1   | 1401_1_3  | 0     | Something2
1401_1   | 1401_1_5  | 1     | Something1
1401_1   | 1401_1_5  | 0     | Something2
1401_1   | 1401_1_7  | 1     | Something1
1401_1   | 1401_1_7  | 1     | Something2
下面是我的代码: 
SELECT * 
FROM (SELECT TOP 12 
            [varchar2] AS [T2], 
            SUM(CASE WHEN [Type] = 'something1' THEN value END) AS something1, 
            SUM(CASE WHEN [Type] = 'something2' THEN value END) AS [something2] 
    FROM tbl_table 
    GROUP by [varchar2] 
    ORDER by [varchar2] DESC)x 
ORDER BY [T2] ASC

该代码产生如下内容:1401_1_1,1401_1_11,1401_1_13,1401_1_15,1401_1_17，1401_1_19, 1401_1_21, 1401_1_23, 1401_1__3, 1401_1_5, 1401_1_7, 1401_1_9 ==>在varchar中排序

我希望它按照以下顺序排序:1401_1_9,1401_1_11,1401_1_13,1401_1_15,1401_1_17,1401_1_19,1401_1_21,1401_1_23,1401_1_1,1401_1_3,1401_1_5,1401_1_7

这可能吗?

如何在sql中使用自定义排序顺序

可以使用下面的代码:

Order by PARSENAME(REPLACE(@varchar2,'_','.'),3),
         PARSENAME(REPLACE(@varchar2,'_','.'),2),
         CASE WHEN PARSENAME(REPLACE([varchar2],'_','.'),1)>=9 THEN 1 ELSE 2 END,
         PARSENAME(REPLACE(@varchar2,'_','.'),1)
相关文章：