在varchar -SQL中按数值排序 [英] Order by numeric value in varchar -SQL

问题描述

我有一列int数据类型，其他列在varchar中，我正在加入联盟..



当前输出

1

10

11

2

20

21

全部



例外输出

全部

1

2

10

11

20

21



我尝试过：



从table1中选择cast（值为varchar）column1

联盟

选择'全部'栏1

按栏目排序

I have a column of int data type and other column is in varchar and I'm joining with union..

Current output
1
10
11
2
20
21
All

Excepted output
All
1
2
10
11
20
21

What I have tried:

Select cast(value as varchar)column1 from table1
Union
Select 'all' column1
Order by column1

推荐答案

ORDER BY [ ^ ]是您所需要的。单击链接以查看如何使用它。所有其他SQL关键字也可以在那里找到完整的例子。

ORDER BY[^] is what you need. Click on the link to see how to use it. All other SQL keywords can be found there also with full examples.

如果要将这些结果传递到表示层然后去除 union ，请使用简单的 order by 并将'All'添加到UI的列表顶部

If you are passing these results into a presentation layer then get rid of the union, use a simple order by and add the 'All' to the top of the list in the UI

请试试这个

Please try this

select * from ( select cast( column1 as nvarchar(5)) as column1
from table1
union 
select column1 from table2) as x
order by 
    case 
        when isnumeric(column1) = 1 then cast(column1 as int)
        when isnumeric(left(column1,1)) = 0 then ascii(left(lower(column1),1)) 
        else 2147483647
    end

这篇关于在varchar -SQL中按数值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！