sql server中的order by子句 [英] order by clause in sql server

问题描述

在我的表中我有一个列STATUS，有这样的记录



ID姓名状态

101 ABCD 1

102 XYZ 2

103 XXX 3

104 DEF 4



i希望以下列格式获得输出



ID NAME STATUS

102 XYZ 2

101 ABCD 1

104 DEF 4

103 XXX 3

解决方案

试试这段代码。将@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

这篇关于sql server中的order by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！