时间戳

首页 > 解决方案 > 对字母数字 SQL Server 列进行排序

sql-server - 对字母数字 SQL Server 列进行排序

问题描述

如何从列中对以下值进行排序Operation_type？

1.Administration
13.Legal And Contractual Matters
14.Financial
15.Personnel
16.Procurement Of Materials, Works And Services
21.Business Management
3.Information System

我尝试了以下方法，但无法执行。

select distinct(Operation_type) 
from tableA  
order by cast(case when Operation_type like '%[0-9]' then right(Operation_type , 1) else null end as int)

以下是预期的输出：

1.Administration
3.Information System 
13.Legal And Contractual Matters
14.Financial
15.Personnel
16.Procurement Of Materials, Works And Services
21.Business Management

PS：我需要获取不同的列值，而不是使用 select *

标签： sql-server

解决方案

You could use CHARINDEX to find the position of the dot (.) in the column and use the numbers before this to ORDER your output:

SELECT *
  FROM tableA  
  ORDER BY CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)

This is entirely dependent on their always being a dot (.) in the column though.

A working fiddle showing this is here.

If you can't guarantee that the dot is always present, this version uses a sub-query to deal with entries that do not contain it:

SELECT Operation_type
  FROM (
        SELECT *,
               CASE
                 WHEN CHARINDEX('.', Operation_type) > 0 THEN CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)
                 ELSE 0
               END AS OrderCol
          FROM tableA  
       ) a
  ORDER BY OrderCol

In this case, the entries without a dot will appear first in the list. You could move them to the end by changing ELSE 0 to a larger value.

Edit following comment about DISTINCT results

The following modified query also handles duplicate rows using DISTINCT as you requested:

SELECT Operation_type
  FROM (
        SELECT DISTINCT(Operation_type),
               CASE
                 WHEN CHARINDEX('.', Operation_type) > 0 THEN CAST(LEFT(Operation_type, CHARINDEX('.', Operation_type) - 1) AS INT)
                 ELSE 0
               END AS OrderCol
          FROM tableA  
       ) a
  ORDER BY OrderCol

推荐阅读