DB中指定值排序

有时候sql语句的排序，不仅仅是 ASC，DESC 简单的正序、倒序排序，而是需要根据指定字段的值，来执行排序，可以使用以下方式来实现

一、简单的排序可使用如下方式：

order by case `state` when 2 then 1 // 当值为2，排在第一个 when 1 then 2 // 当值为1，排在第二个 when 3 then 3 // 当值为3，排在第三个 end asc // 按上面顺序，正序排列（也可为desc）

二、稍复杂的排序

select * from session.results 
          order by case when tradename='合计' then 1 
                        when p_showTrade='3' and tradename='一' then 2 
                        when p_showTrade='3' and tradename='一一then 3 
                        when p_showTrade='3' and tradename='一二' then 4 
                        when p_showTrade='3' and tradename='一三' then 5 
                        when p_showTrade='3' and tradename='一四' then 6 
                        when p_showTrade='3' and tradename='二' then 7 
                        when p_showTrade='3' and tradename='三' then 8 
                        when p_showTrade='3' and tradename='三一' then 9 
                        when p_showTrade='3' and tradename='三二' then 10 
                        when p_showTrade='3' and tradename='四' then 11 
                        when p_showTrade='3' and tradename='五' then 12 
                        else 2 end,sale desc;

//如果满足指定条件p_showTrade='3' ，按指定条件和指定的值排序，否则按照销售量降序排序

三、最复杂排序

灵感MYSQL order by 根据不同条件升序或降序_Lan77-CSDN博客_mysql降序的条件怎么添加 

不同的条件下，显示的结果不同，第一个结果集只显示名称，第二个条件下显示区，省，市，第三个结果显示外省下的市，三个结果集排序条件不同。

方法一：使用union all

错误：

SELECT * FROM table_a ORDER BY CASE WHEN type = 1 THEN 'age DESC' WHEN type = 2 THEN 'age ASC' END

正确：

SELECT * FROM table_a WHERE type = 1 ORDER BY age DESC

UNION ALL

SELECT * FROM table_a WHERE type = 2 ORDER BY age ASC

方法二：当type = 2的时候把age字段变成负数，然后全部降序排序

SELECT type ,(CASE WHEN type = 1 THEN age * 1 WHEN type = 2 THEN age * -1 END) field FROM table_a ORDER BY field DESC

 DECLARE v_captionCur CURSOR WITH RETURN TO CALLER FOR
	    	SELECT * FROM  SESSION.captions ORDER BY ordertag;
	    DECLARE v_resultCur CURSOR WITH RETURN TO CALLER FOR
	        SELECT * FROM session.results 
	        order by
             case when trim(in_tobacode)='1' and trim(in_provcode)='1' then wholesaleprice*-1
             when trim(in_tobacode)<>'1' and trim(in_provcode)='1' then tag
             when trim(in_tobacode)<>'1' and trim(in_provcode)='1' then speedradio*-1
             when trim(in_tobacode)<>'1' and trim(in_provcode)<>'1' then speedradio*-1
             end