SQL Server sorting summary by specific keywords

Create test Table1

 
Create Table Table1 (ID int, name char) insert into table1select 1, 'q' Union all select 2, 'R' Union all select 3, '3' Union all select 4, '5'
 

 

Table 1 data needs to be obtained in the specified ID Order (such ,).

Method 1:Union all is used, but can only be sorted by a maximum of 256 keywords.

 
Select ID, name from Table1 where id = 2 Union allselect ID, name from Table1 where id = 1 Union allselect ID, name from Table1 where id = 4 union allselect ID, name from Table1 where id = 3
 

Method 2:Use Case when in order
 

 
Select ID, 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 small data volumes.

Method 3:Use cursors and temporary tables
Create a secondary table first, and insert it in the sequence you need, for example

 

Create Table T1 (id int) insert into t1select 2 Union all select 1 Union all select 4 union all select 3 declare @ ID int -- defines the cursor declare c_test cursor forselect ID from T1 select * into # TMP from Table1 where 1 = 2 -- construct the temporary table structure open c_test fetch next from c_test into @ idwhile @ fetch_status = 0begin -- insert data to the temporary table in the order of ID in T1 into 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 applicable when the table needs to be rearranged in the order of the secondary table.
(That is, the auxiliary table already exists)

Method 4:Split string Parameters

Select * into # TMP from Table1 where 1 = 2 -- construct the temporary table structure declare @ STR varchar (300), @ ID varchar (300), @ M int, @ n int set @ STR = '2, 3, '--- Note that 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
 

* This method is more universal.

Test Results
ID name
---------------
2 R
1 Q
4 5
3 3

(The number of affected rows is 4)

PS (postscript): I prefer to use the fourth method. When there are not many sorting conditions, write them directly.

Order by charindex (field name, 'condition 1, condition 2, condition 3 ')