表的某个字段按需排序(快速结贴)解决办法

表的某个字段按需排序(快速结贴)
原始数据为：
[code =SQL]
Uid(varchar) uname result
00EF 张三 Approving
76A1 马六created
7EAC 钱七Apprved succeed
5BA4 李五decline
4A87 王四 deleted
[/code]

想要对result字段进行排序，显示顺序优先级为(created->decline->Apprved succeed->Approving->deleted)

即想要结果：
[code =SQL]
Uid uname result
76A1 马六created
5BA4 李五decline
7EAC钱七Apprved succeed
00EF 张三 Approving
4A87王四deleted
[/code]

请问SQL如何写呢?(使用 [code =SQL]order by case result when....end [/code] ?)



[解决办法]

SQL code

DECLARE @tab TABLE([Uid] VARCHAR(10),uname VARCHAR(10),result VARCHAR(20))DECLARE @t TABLE (id INT ,[DESC] VARCHAR(20))INSERT INTO @tabSELECT '00EF', '张三', 'Approving' UNION ALLSELECT '76A1', '马六', 'created' UNION ALLSELECT '7EAC', '钱七', 'Apprved succeed' UNION ALLSELECT '5BA4', '李五', 'decline' UNION ALLSELECT '4A87', '王四', 'deleted' INSERT INTO @tSELECT 1,'created' UNION ALLSELECT 2,'decline' UNION ALLSELECT 3,'Apprved succeed' UNION ALLSELECT 4,'Approving' UNION ALLSELECT 5,'DELETED' SELECT t1.* FROM @tab t1 INNER JOIN @t t2 ON t1.result=t2.[DESC] ORDER BY t2.id/*Uid        uname      result---------- ---------- --------------------76A1       马六         created5BA4       李五         decline7EAC       钱七         Apprved succeed00EF       张三         Approving4A87       王四         deleted*/
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba( Uid varchar(10),  uname VARCHAR(10),  result VARCHAR(20))  INSERT INTO tbaSELECT '00EF', '张三', 'Approving' UNIONSELECT '76A1', '马六', 'created' UNIONSELECT '7EAC', '钱七', 'Apprved succeed' UNIONSELECT '5BA4', '李五', 'decline' UNIONSELECT '4A87', '王四', 'deleted'SELECT Uid,Uname,resultFROM tbaORDER BY CASE result WHEN 'created' THEN 1                     WHEN 'decline' THEN 2                     WHEN 'Apprved succeed' THEN 3                     WHEN 'Approving' THEN 4                     WHEN 'deleted' THEN 5 END
[解决办法]
修改一下失误
with a as (
select 1 id,'created' rs  union  
select 2,'decline' union
select 3,'Apprved' union
select 4,'succeed' union
select 5,'Approving' union  
select 6,'deleted')
select tb.*  from tb join a on tb.result=a.rs order by id

探讨


SQL code

with a as (
select 1 id,'created' rs  union 
select 2,'decline' union
select 3,'Apprved' union
select 4,'succeed' union
select 5,'Approving' union 
select 6,'deleted')
select *  from tb jo……