Original post address: http://community.9cbs.net/expert/topic/3693/3693091.xml? Temp = .6086542HTTP: //community.9cbs.net/expert/topicview.asp? Id = 3724743 Test TableCreate Table Table1 (ID INT, Name Char) INTO TABLE1SELECT 1, 'Q'Union All Select 2,' R'Union All Select 3, '3'Union All SELECT 4,' 5 'Requires in the specified ID (such as 2, 1 , 4, 3) Data Method 1: Use Union All, but there are 256 data limitations Select ID, name from table1 where id = 2Union allselect id, name from table1 where id = 1Union allselect ID, Name from table1 Where id, name from table1 where id = 3 method 2: Using Case WHENSELECT ID in ORDER BY, Name from T Where ID in (2, 1, 4, 3) Order By (Case ID When 2 Then) A 'WHEN 1 THEN' B 'WHEN 4 THEN' C 'WHEN 3 THEN' D 'END * The above two methods are suitable for use in the case of very small data 3: use the cursor and temporary table to build a secondary table , Inserted in the order you need, such as 2, 1, 4, 3create Table T1 (ID INT) INSERT INTO T1SELECT 2UNION All SELECT 1UNION All Select 4UNION All SELECT 3
Declare @ID int - Defining Cursor Declare C_Test Cursor Fort From T1 Select * INTO #TMP from Table1 Where 1 = 2 - Constructs the structure of a temporary table
Open c_test
FETCH NEXT FROM c_test INTO @idWHILE @@ FETCH_STATUS = 0BEGIN-- id by t1 in order interpolation data into the temporary table insert into #tmp select id, name from table1 where id = @ id FETCH NEXT FROM c_test INTO @idEndClose c_test deallocate c_test * This method is suitable for use in the order of rearrangement of Table in the order of the auxiliary table (ie the case already exists)
Method 4: Segment string parameter Select * INTO #TMP from table1 where 1 = 2 - Constructs the structure of a temporary table
Declare @str varchar (300), @ ID var, @n int set @ Str = '2, 1, 4, 3,' --- Note behind there is a comma set @ m = charIndex ( ',', @ STR) set @ n = 1 while @m> 0 begin set @ id = Substring (@ Str, @ n, @ m- @ n) --print @id INSERT INTO #TMP SELECT ID, NAME FROM Table1 WHERE ID = Convert (int, @ ID) set @ n = @ m 1 set @ m = charindex (',', @ Str, @ n) end method 5: Using charIndexselect * from T1 Order by Charindex (Convert) VARCHAR (2), ID), '2, 1, 4, 3') Test Results ID Name ----------- ---- 2 R1 Q4 53 3
(The number of rows affects is 4 lines)
