有时候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