在SQL服务器中按asc排序 [英] Order by asc in SQL server

问题描述

我需要根据降值对数字进行排序。样本数据是

i need to sort the number from asscending value. sample data is

463919493       
463919493 02
463919493 02
463919493 03
463919493 01
463919493 03
463919493 01

i需要获得输出

i need to get output as

463919493       
463919493 01
463919493 01
463919493 02
463919493 02
463919493 03
463919493 03

并假设我们有数字字母，如何对这个数字进行排序

and suppose if we have alphabet with number, how to sort this number in asscending

HO463919493       
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 01
HO463919493 03
HO463919493 01

需要输出为：

Need to get output as:

HO463919493       
HO463919493 01
HO463919493 01
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 03

我的尝试：

What I have tried:

463919493       
463919493 02
463919493 02
463919493 03
463919493 01
463919493 03
463919493 01

i需要获得输出

i need to get output as

463919493       
463919493 01
463919493 01
463919493 02
463919493 02
463919493 03
463919493 03

如果我们有数字字母，如何按顺序排序这个数字

and suppose if we have alphabet with number, how to sort this number in asscending

HO463919493       
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 01
HO463919493 03
HO463919493 01

HO463919493       
HO463919493 01
HO463919493 01
HO463919493 02
HO463919493 02
HO463919493 03
HO463919493 03

推荐答案

如果您想将文本数据视为数字，则必须将其转换。例如：

If you would like to treat text data as numeric, you have to convert it. For example:

SELECT * 
FROM (
    SELECT 'HO463919493' AS F1, NULL AS F2
    UNION ALL       
    SELECT 'HO463919493', '02'
	UNION ALL       
    SELECT 'HO463919493', '02'
	UNION ALL       
    SELECT 'HO463919493', '03'
	UNION ALL       
    SELECT 'HO463919493', '01'
	UNION ALL       
    SELECT 'HO463919493', '03'
	UNION ALL       
    SELECT 'HO463919493', '01'
) AS T
ORDER BY CONVERT(INT, SUBSTRING(F1, 3, LEN(F1))) ASC, CONVERT(INT, F2) ASC

注意：上面的示例是针对特定数据的，前两个字母在转换过程中被拒绝。

Note: Above sample is for specific data, where first two letters are rejected during conversion.

要添加到两个解决方案：为什么要存储首先是单个列中的两个单独值？如果您将值都放在不同的列中，那么您的SQL操作会更容易。然后你可以简单地使用ORDER BY子句来订购，例如

To add to both solutions: Why do you store the two separate values in a single column in the first place? Your SQL operations would be much easier if you would have the values both in separate columns. Then you could simply order the by using an ORDER BY clause like

ORDER BY Col2 ASC, Col1 ASC

你没有向我们展示任何SQL查询。



通常你必须追加

You have not shown us any SQL query.

Usually you have to append

ORDER BY [columnname] ASC

到您的查询，其中 columnname 是您的第二列的名称。因为 ASC 是默认的排序顺序，所以可以省略它。



您还可以指定多列或更复杂的表达式。请参阅订购BY子句（Transact-SQL）| Microsoft Docs [ ^ ]了解更多信息。

to your query where columnname is the name of your second column. Because ASC is the default sort order it can be omitted.

You can also specify multiple columns or more complex expressions. See ORDER BY Clause (Transact-SQL) | Microsoft Docs[^] for more information.

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