如何在多个条件下解码，条件相同，结果不同
如何在多个条件下解码，条件相同，结果不同
提问于 2014-11-21 01:23:27
我有一个问题，我如何评估一个sql，当id=5在‘目录帮助’的情况下，它应该得到attr_1作为输出，而在id=5和'Long Roaming‘的情况下，它应该给出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回答 1
Stack Overflow用户
回答已采纳
发布于 2014-11-21 01:44:01
首先，我将封装case逻辑以及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')
)然后，在CTE上执行group by (这避免了必须编写两次CASE逻辑)：
select
usagetype
-- , <sum term will go here>
from ce group by usagetype第三，由于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然后，您可以在您的案例中组合第一个和第二个术语：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27045732
复制相关文章
相似问题
腾讯云开发者
