如何按照在 SQL Server 中输入的顺序进行排序? [英] How to SORT in order as entered in SQL Server?
本文介绍了如何按照在 SQL Server 中输入的顺序进行排序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 SQL Server,我正在尝试查找结果,但我希望以与输入条件相同的顺序获得结果.
我的代码:
SELECT帐号,结束日期从帐户在哪里AccountNumber IN (212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689) -- 我希望结果与这些数字的顺序相同.
解决方案
这是一个内联方法
示例
声明@List varchar(max)='212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689'选择 A.AccountNumber,A.结束日期来自账户 A加入 (选择 RetSeq = Row_Number() over (Order By (Select null)),RetVal = v.value('(./text())[1]', 'int')From (values (convert(xml,'<x>' + replace(@List,',','</x><x>')+'</x>'))) x(n)交叉应用 n.nodes('x') node(v)) B on A.AccountNumber = B.RetVal按 B.RetSeq 排序
<块引用>
EDIT - 子查询返回
RetSeq RetVal1 2123452 3123453 1456874 6589755 2569876 3658747 5689748 1245789 125689
I'm using SQL Server and I'm trying to find results but I would like to get the results in the same order as I had input the conditions.
My code:
SELECT
AccountNumber, EndDate
FROM
Accounts
WHERE
AccountNumber IN (212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689) -- I would like the results to be in the same order as these numbers.
解决方案
Here is an in-line approach
Example
Declare @List varchar(max)='212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689'
Select A.AccountNumber
,A.EndDate
From Accounts A
Join (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = v.value('(./text())[1]', 'int')
From (values (convert(xml,'<x>' + replace(@List,',','</x><x>')+'</x>'))) x(n)
Cross Apply n.nodes('x') node(v)
) B on A.AccountNumber = B.RetVal
Order By B.RetSeq
EDIT - the subquery Returns
RetSeq RetVal
1 212345
2 312345
3 145687
4 658975
5 256987
6 365874
7 568974
8 124578
9 125689
这篇关于如何按照在 SQL Server 中输入的顺序进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文