oracle 如何使用具有不同结果的相同条件的多个条件进行解码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27045732/
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
how to decode with multiple conditions with same condition with diff result
提问by user3274607
I have a question, how can i evaluate a sql, when id=5 in the case of 'Directory Assistance' it should get the attr_1 as an output and with id=5 and 'Long Roaming' it should give attr_35/60.
我有一个问题，我如何评估 sql，当 id=5 在“目录帮助”的情况下，它应该得到 attr_1 作为输出，当 id=5 和“长漫游”时，它应该给出 attr_35/60。
sum(decode(id,1,attr_35 / 60,
5,'Long Roaming',attr_35/60,
5,'Directory Assistance',attr_1))total
with ce as
(
select case
when id = 1 and attr_31 like 'Roam%'
then 'A1'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
then 'Directory Assistance'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
then 'Directory Assistance Roaming'
when id = 5 and attr_30 like 'Long Distance%' and attr_31 like 'Roam%'
then 'Long Roaming'
end usagetype
, sum(decode(id,1,attr_35 / 60, 5,attr_35/60)) total
from table
where ce.account_num in ('A4','A5','A6')
group by
case
when id = 1 and attr_31 like 'Roam%'
then 'A1'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
then 'Directory Assistance'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
then 'Directory Assistance Roaming'
when id = 5 and attr_30 like 'Long Distance%'and attr_31 like 'Roam%'
then 'Long Roaming'
end
)
select usagetype,total from ce
采纳答案by davek
First, I would encapsulate the case logic plus any other columns you may need in your CTE:
首先，我将封装案例逻辑以及您在 CTE 中可能需要的任何其他列：
with ce as
(
select
case
when id = 1 and attr_31 like 'Roam%'
then 'A1'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
then 'Directory Assistance'
when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
then 'Directory Assistance Roaming'
when id = 5 and attr_30 like 'Long Distance%' and attr_31 like 'Roam%'
then 'Long Roaming'
else '-'
end usagetype
, id
, attr_30
, attr_31
, attr_35
from table
where ce.account_num in ('A4','A5','A6')
)
Then, perform the group by on the CTE (this avoids having to write the CASElogic twice):-
然后，在 CTE 上执行 group by（这避免了必须编写CASE两次逻辑）：-
select
usagetype
-- , <sum term will go here>
from ce group by usagetype
Third, since decode can only work on a single column/value at a time, you will need a second case:
第三，由于 decode 一次只能处理一个列/值，您将需要第二个case：
select
usagetype
, sum(case
when id = 1 then
attr_35 / 60
when id = 5 and usagetype = 'Long Roaming' then
attr_35 / 60
when id = 5 and usagetype = 'Directory Assistance' then
attr_1
else
0
end) as total_result
from ce group by usagetype
You could then combine the first and second terms in your case:
然后，您可以在您的情况下结合第一项和第二项：
select
usagetype
, sum(case
when id = 1 or (id = 5 and usagetype = 'Long Roaming') then
attr_35 / 60
when id = 5 and usagetype = 'Directory Assistance' then
attr_1
else
0
end) as total_result
from ce group by usagetype
