如何在上一列满足一定条件时使用SQL选择下一列

Question

我在Impala上使用SQL

我正在查询的桌子看起来就像

客户名称shop1 shop1number shop2 shop2number shop3 shop3number

TOM AB 111 AA 231 AC 321

艾米AC 121 AB 213 AD 231

法兰克AD 123 AE 233 AB 234

在这里输入图像描述这里的数字是顾客忠诚度号码，而挑战是，如果我在寻找商店1 (AB)的忠诚度号码，我不知道它在哪一列，因为当客户填写他们的忠诚度号码时，他们会选择按他们的个人资料按任何顺序输入这个号码。

Answer 1

如果我对您的理解是正确的，您将查找与商店相关的所有忠诚度数字，因此一种方法可能是首先使用union all将行数据带到列中，然后搜索一个商店，比如AB。

select * from
(
select customername, shop1 as shop, shop1number as shopnumber
from table1
union all
select customername, shop2 as shop, shop2number as shopnumber
from table1
union all
select customername, shop3 as shop, shop3number as shopnumber
from table1
    ) t
where t.shop = 'AB';

结果：

+--------------+------+------------+
| customername | shop | shopnumber |
+--------------+------+------------+
| AMY          | AB   |        213 |
| TOM          | AB   |        111 |
| Franck       | AB   |        234 |
+--------------+------+------------+

演示

Answer 2

declare @shop varchar(10)
set @shop='AB'
select cname, 
case when  shop1=@shop then shop1 
when shop2=@shop then shop2
when shop3=@shop then shop3
end
as shop, 
shop1number as shopnumber
from tblcus