SQL sql中的条件order by子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14597695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
conditional order by clause in sql
提问by Ram Dutt Shukla
I have a query that should order the result in asc or desc depending upon a column value.
我有一个查询应该根据列值按 asc 或 desc 对结果进行排序。
e.g.
例如
if employee of type manager exists THEN order by joining_date, bith_date ASC else if employee is developer THEN order by joining_date, birth_date DESC.
如果类型为 manager 的员工存在 THEN order by join_date, bith_date ASC 否则如果员工是开发者 THEN order by join_date,birth_date DESC。
I would like to achieve something like below, but can't achieve that.
我想实现以下目标，但无法实现。
ORDER BY CASE WHEN employee_type = 'm'
THEN joining_date, birth_date ASC;
WHEN employee_type = 'd'
THEN joining_date, birth_date DESC;
回答by Ram Dutt Shukla
Well I got the answer after some research.
好吧，经过一番研究，我得到了答案。
We can add multiple columns in where clause conditionally as follows :
我们可以有条件地在 where 子句中添加多列，如下所示：
ORDER BY DECODE(employee_type, 'm', joining_date, birth_date, salary) ASC,
DECODE(employee_type, 'd', joining_date, birth_date, salary) DESC
This will order the result on the basis of employee_type.
这将根据employee_type 对结果进行排序。
回答by ypercube??
I suspect you want something like this:
我怀疑你想要这样的东西：
ORDER BY
employee_type DESC -- first all the managers, then the developers
-- and in every one of these two groups
, joining_date -- first order by joining date
, CASE WHEN employee_type = 'm' -- and then either by
THEN birth_date -- birth date ascending for managers
ELSE NULL
END -- or
, birth_date DESC ; -- birth date descending for the rest (devs)
回答by Florin Ghita
The question is a little bit poor specified.
这个问题的说明有点差。
order the result in asc or desc depending upon a column value.
根据列值按 asc 或 desc 对结果进行排序。
A column takes many values (as there are multiple rows).
一列有很多值（因为有多行）。
Now, order byclause use an expression and order rows upon it.
That expression should be morphotropic(;))
现在，order by子句使用表达式并对其排序。该表达式应该是morphotropic(;))
So, assuming stardard oracle's employee schema, managers are:
因此，假设标准 oracle 的员工架构，经理是：
select *
from emp e
where exists (select emp_id from emp where e.id=emp.mgr_id)
An workaround query may be:
解决方法查询可能是：
Select e.id, e.name, e.birth_date,
case
when (select count(*)
from emp e
where exists (select emp_id from emp where e.id=emp.mgr_id)
) --existence of manager
> 0 then birth_date - to_date('1-Jan-1000','dd-mon-yyyy')
else to_date('1-Jan-1000','dd-mon-yyyy') - birth_date
end as tricky_expression
from emp A
order by 4;
That exexpresion is the case; Using a constant(subquery that decides there are managers) it changes values from positive to negative, that is, change the order direction.
该表达式是case; 使用常量（决定存在管理器的子查询）将值从正变为负，即更改顺序方向。
UPDATE:with the details in the comments:
更新：评论中的详细信息：
select id, name, birth_date emp_type
from (
Select id, name, birth_date, emp_type,
case when cnt_mgr > 0 then birth_date - to_date('1-Jan-1000','dd-mon-yyyy')
else to_date('1-Jan-1000','dd-mon-yyyy') - birth_date
end as tricky_expression
from(
Select e.id, e.name, e.birth_date, emp_type,
count(case when emp_type='M' then 1 else 0 end) over() as mgr_count
from emp A
where your_conditions
)
order by tricky_expression
)
where rownum=1;
回答by Egor Skriptunoff
If there is a manager in the company this query returns the oldest manager, otherwise - the youngest developer.
如果公司中有经理，此查询将返回最老的经理，否则 - 最年轻的开发人员。
select
id, name, birth_date, emp_type
from emp
where
id = (select
max(id) keep (dense_rank first order by
decode(emp_type, 'M', 1, 'D', 2),
joining_date,
decode(emp_type, 'M', 1, 'D', -1) * (birth_date - to_date('3000','yyyy')))
from emp)
