我在Impala 上使用SQL
我查询的表看起来像
客户名称商店1商店1编号商店2商店2编号商店3商店3编号
TOM AB 111 AA 231 AC 321
AMY AC 121 AB 213 AD 231
弗兰克AD 123 AE 233 AB 234
在此处输入图像描述这里的数字是客户忠诚度号码,挑战是如果我在寻找商店1(AB)的忠诚度号码,我不知道它在哪一列,因为当客户填写他们的忠诚度号码时,他们可以选择按他们配置的任何顺序输入号码
如果我理解正确,您正在查找与商店相关的所有忠诚度编号,因此一种方法可以是首先使用union all
将行数据带到列中,然后搜索商店;比方说CCD_ 2。
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 |
+--------------+------+------------+
演示
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