22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @str VARCHAR(max)='4,5,6,-1,7,8,9,-2,10,11,12,-3,1,2,3-4,0'
SELECT * FROM table1 WHERE CHARINDEX(','+RTRIM(ID)+',',@str)>0 ORDER BY CHARINDEX(','+RTRIM(ID)+',',','+@str+',')
/* 测试数据
WITH t(col1,col2) AS (
SELECT 4,'A' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 6,'C' UNION ALL
SELECT 7,'D' UNION ALL
SELECT 8,'E' UNION ALL
SELECT 9,'F' UNION ALL
SELECT 10,'G' UNION ALL
SELECT 11,'H' UNION ALL
SELECT 12,'I' UNION ALL
SELECT 1,'J' UNION ALL
SELECT 2,'K' UNION ALL
SELECT 3,'L' UNION ALL
SELECT 0,'M' UNION ALL
SELECT -1,'N' UNION ALL
SELECT -2,'O' UNION ALL
SELECT -3,'P' UNION ALL
SELECT -4,'Q'
)
SELECT * INTO table1 FROM t
*/
-- 查询
;WITH a(col1,sort) AS (
SELECT 4,1 UNION ALL
SELECT 5,2 UNION ALL
SELECT 6,3 UNION ALL
SELECT -1,4 UNION ALL
SELECT 7,5 UNION ALL
SELECT 8,6 UNION ALL
SELECT 9,7 UNION ALL
SELECT -2,8 UNION ALL
SELECT 10,9 UNION ALL
SELECT 11,10 UNION ALL
SELECT 12,11 UNION ALL
SELECT -3,12 UNION ALL
SELECT 1,13 UNION ALL
SELECT 2,14 UNION ALL
SELECT 3,15 UNION ALL
SELECT -4,16 UNION ALL
SELECT 0,17
)
SELECT table1.*
FROM table1
JOIN a
ON table1.col1 = a.col1
ORDER BY a.sort
col1 col2
----------- ----
4 A
5 B
6 C
-1 N
7 D
8 E
9 F
-2 O
10 G
11 H
12 I
-3 P
1 J
2 K
3 L
-4 Q
0 M