表的某个字段按需排序(快速结贴)
原始数据为：
[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