试试这段代码。将@t替换为您的表名。

 选择 id，NAME ，
 case  
当 status = 2 然后  1  
当 status = 1 然后  2  
当 status = 4    3  
  status = 3 然后  4  
 end   as  newstatus 
来自  @ t  
订单 按 newstatus 

假设你有一个状态t能够包含所有提供的状态。您可以在名为ORDERING_INFO的状态表中添加一列。

  ALTER  表状态 ADD  ORDERING_INFO  INT   NULL  

更新状态 SET  ORDERING_INFO =  2   WHERE  STATUS_ID =  1 ; 
更新状态 SET  ORDERING_INFO =  1   WHERE  STATUS_ID =  2 ; 
更新状态 SET  ORDERING_INFO =  4   WHERE  STATUS_ID =  3 ; 
更新状态 SET  ORDERING_INFO =  3   WHERE  STATUS_ID =  4 ; 

然后你可以像这样修改你的选择查询

  SELECT  ID，NAME 
 FROM   TABLE  
 INNER   JOIN 状态 ON   TABLE  .STATUS = STATUS.STATUS_ID 
 ORDER   BY 状态。 ORDERING_INFO 

hi,


in my table i am having a column STATUS which is having records like this

ID NAME STATUS
101 ABCD 1
102 XYZ 2
103 XXX 3
104 DEF 4

i want to get output in the following format

ID NAME STATUS
102 XYZ 2
101 ABCD 1
104 DEF 4
103 XXX 3

Try this code. Replace @t with your table name.

select id,NAME,
case
when status=2 then 1
when status=1 then 2
when status=4 then 3
when status=3 then 4
end as newstatus
from @t
order by newstatus

Assuming you have a status table that contains all the provided status. You can add a column in that status table named ORDERING_INFO.

ALTER TABLE STATUS ADD ORDERING_INFO INT NULL

UPDATE STATUS SET ORDERING_INFO = 2 WHERE STATUS_ID = 1;
UPDATE STATUS SET ORDERING_INFO = 1 WHERE STATUS_ID = 2;
UPDATE STATUS SET ORDERING_INFO = 4 WHERE STATUS_ID = 3;
UPDATE STATUS SET ORDERING_INFO = 3 WHERE STATUS_ID = 4;

You can then modify your select query like this

SELECT ID,NAME
FROM   TABLE 
       INNER JOIN STATUS ON TABLE.STATUS = STATUS.STATUS_ID
ORDER BY STATUS.ORDERING_INFO