SQL Hive 中的嵌套 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33746185/
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
Nested Case Statements in Hive
提问by tzhang94
Does anyone have an idea on why this code isn't working?
有没有人知道为什么这段代码不起作用？
create table2 as
select
*,
1 as count,
case
when a=1 then
case
when tx="A_L" then "L"
when tx="B_A" then "A"
when tx="C_E" then "E"
when tx in ("E_V","D_M","H_O","I_D") then "Other"
when tx="F_S" then "S"
when tx="G_L" then "L"
end
when b=1 then
case
when tx="A_L" then "L"
when tx="B_A" then "A"
when tx="C_E" then "E"
end
else
case
when tx="A_L" then "L"
when tx="B_A" then "A"
when tx="C_E" then "E"
when tx in ("D_M","E_V","F_S","H_O","I_D") then "Other"
when tx="G_L" then "L"
end
end as tx1
from table1
Or is there a simpler way to do this? I'm writing this within a proc sql statement and pushing it to Hadoop (so it needs to be HiveQL compatible).
或者有没有更简单的方法来做到这一点？我在 proc sql 语句中编写它并将其推送到 Hadoop（因此它需要与 HiveQL 兼容）。
回答by CiscoJavaHadoop
Combine nested Case statements into one case statement using either AND or OR clause.This will work.
使用 AND 或 OR 子句将嵌套的 Case 语句组合成一个 case 语句。这将起作用。
回答by user3123372
Instead of testing , this one is more readable.
而不是 testing ，这个更具可读性。
create table2 as
select
*,
1 as count,
case
when a=1 and tx="A_L" then "L"
when a=1 and tx="A_L" then "L"
when a=1 and tx="B_A" then "A"
when a=1 and tx="C_E" then "E"
when a=1 and tx in ("E_V","D_M","H_O","I_D") then "Other"
when a=1 and tx="F_S" then "S"
when a=1 and tx="G_L" then "L"
when b=1 and tx="A_L" then "L"
when b=1 and tx="B_A" then "A"
when b=1 and tx="C_E" then "E"
when a!=1 and b!=1 and tx="A_L" then "L"
when a!=1 and b!=1 and tx="B_A" then "A"
when a!=1 and b!=1 and tx="C_E" then "E"
when a!=1 and b!=1 and tx in ("D_M","E_V","F_S","H_O","I_D") then "Other"
when a!=1 and b!=1 and tx="G_L" then "L"
end as tx1
from table1
