34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT tb.*
FROM dbo.Nums tb
INNER JOIN dbo.Split('1,3,5,2,4,6',',') l
ON tb.n = l.v
ORDER BY l.i
CREATE FUNCTION dbo.Split(
@string nvarchar(max),
@separator nvarchar(10) = N','
)
RETURNS TABLE
RETURN
SELECT
i = ROW_NUMBER() OVER(ORDER BY (SELECT 1)),
v = x.n.value('.','nvarchar(50)')
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = @string FOR XML PATH('')),@separator,'</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
CREATE TABLE #temp(i int NOT NULL UNIQUE, seq int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED)
INSERT INTO #temp
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 6
SELECT tb.*
FROM mytable tb
INNER JOIN #temp tmp
ON tb.i = tmp.i
ORDER BY tmp.seq
select row_number() over(partition by getdate() order by getdate()) aid,*
from tb where id in (2,4,3,5) order by aid
--SQL 2005的写法。