There are two variables in our database called OB and B2B_OB. There are EP codes and each EP may have one of the following 4 combinations:
我们的数据库中有两个变量叫做OB和B2B_OB。有EP代码，每个EP可能有以下4种组合之一：
EP OB B2B_OB
--- -- ------
3 X
7 X
11
14 X X
What I want to do is to combine the X's in the fetched list so that I can have A or B value for a single EP. My conditions are:
我想要做的是在提取的列表中组合X，以便我可以为单个EP提供A或B值。我的条件是：
IF (OB IS NULL AND B2B_OB IS NULL) THEN TYPE = A
IF (OB IS NOT NULL OR B2B_OB IS NOT NULL) THEN TYPE = B
The list I want to fetch is like this one:
我想要获取的列表就像这样：
EP TYPE
--- ----
3 B
7 B
11 A
14 B
How can I do this in my query? TIA.
我怎么能在我的查询中这样做？ TIA。
1 个解决方案
#1
4
(EDIT: corrected AND to OR)
Here's a solution (syntax tested on postgres, but from what I read it should be the same)
（编辑：更正AND到OR）这是一个解决方案（在postgres上测试语法，但从我读到的应该是相同的）
select ep,
case
when ob is null and b2b_ob is null then a
when ob is not null or b2b_ob is not null then b
else null
end as type
from table;
Note: else null is redundant but only wanted to emphasize the default else case.
注意：else null是多余的，但只想强调默认的else情况。
#1
4
(EDIT: corrected AND to OR)
Here's a solution (syntax tested on postgres, but from what I read it should be the same)
（编辑：更正AND到OR）这是一个解决方案（在postgres上测试语法，但从我读到的应该是相同的）
select ep,
case
when ob is null and b2b_ob is null then a
when ob is not null or b2b_ob is not null then b
else null
end as type
from table;
Note: else null is redundant but only wanted to emphasize the default else case.
注意：else null是多余的，但只想强调默认的else情况。