如何选择下一列时,前一列满足使用SQL
问题描述:
我使用SQL对帕拉如何选择下一列时,前一列满足使用SQL
一定的条件,我查询的表格看起来像
客户名称shop1 shop1number SHOP2 shop2number shop3 shop3number
TOM AB 111 AA 231 321 AC
AMY AC 121 AB 213 AD 231
弗兰克AD AE 123 233 234 AB
enter image description here 这里,数字是客户忠诚度数字,如果我在寻找店铺1(AB)的忠诚度数字,我不知道客户填写忠诚度时,号,这是他们的选择就摆在不惜一切为了他们个人资料
答
如果我理解正确的话,你正在寻找一个店铺相关的所有忠诚编号的数字,所以一个方法可以把行数据列第一使用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 |
+--------------+------+------------+
答
declare @shop varchar(10)
set @shop='AB'
select cname,
case when [email protected] then shop1
when [email protected] then shop2
when [email protected] then shop3
end
as shop,
shop1number as shopnumber
from tblcus
请您可以使表格设计更清晰一点?也许提供一张图片? – majjam
这是3排吗? '店1/AAA /店铺2/BBB /店铺3/CCC'或在同一行上的单个列? –
没有那是一排有6列,因此该表看起来像 –