关于Select Where In的排序问题
有很多人不知道SQL里怎么按 Select Where In 的内容进行字段排序.
假如SQL语句为:
1
2
3
|
Select
*
FROM
table1
Where
(ID
IN
(3,5,1,4,2))
|
如果In后面的条件都是数字,那MSSQL排序应该为
1
2
3
4
|
Select
*
FROM
table1
Where
(ID
IN
(3,5,1,4,2))
ORDER
BY
CHARINDEX(
','
+
CONVERT
(nvarchar, ID) +
','
,
','
+
CONVERT
(nvarchar,
Replace
(
'3,5,1,4,2'
,
' '
,
''
)) +
','
)
|
改进方法,不去处理空格,直接改用空格判断.可以用来判断少数有空格的字符条件.
1
2
3
4
|
Select
*
FROM
table1
Where
(ID
IN
(3,5,1,4,2))
ORDER
BY
PATINDEX(
'% '
+
CONVERT
(nvarchar(4000), ID) +
' %'
,
' '
+
CONVERT
(nvarchar(4000),
Replace
(
'3,5,1,4,2'
,
','
,
' , '
)) +
' '
)
|
其实还是在SQL外处理好条件字符串再进行查询和排序比较好.
对于MYSQL排序可能要改为:
1
2
3
4
|
Select
*
FROM
table1
Where
(ID
IN
(3,5,1,4,2))
ORDER
BY
FIND_IN_SET(ID,
'3,5,1,4,2'
)
|