代码之家 › 专栏 › 技术社区 › galih

sql-对3列中的匹配值进行分组

sql mysql

galih · 技术社区 · 7 年前

我有一个名为trx\u data的表,假设它包含:

issuer  acquirer    destination
A       A           C
A       B           A
B       A           A
B       A           C
C       B           A
A       B           C

我想把A,B,C分为:

仅收单机构价值

值仅作为目标

作为发卡机构和收单机构的价值

作为收单机构和目的地的价值

这是我的密码

select bank_role, count(*) from(
select
issuer,acquirer,destination,
case
when issuer="A" and acquirer="A" and destination<>"A" then "A as issuer-acquirer"
when issuer="A" and acquirer<>"A" and destination="A" then "A as issuer-destination"
when issuer<>"A" and acquirer="A" and destination="A" then "A as acquirer-destination"
when issuer="A" and acquirer<>"A" and destination<>"A" then "A as issuer only"
when issuer<>"A" and acquirer="A" and destination<>"A" then "A as acquirer only"
when issuer<>"A" and acquirer<>"A" and destination="A" then "A as destination only"
else "unknown"
end as bank_role
from trx_data
union all
select
issuer,acquirer,destination,
case
when issuer="B" and acquirer="B" and destination<>"B" then "B as issuer-acquirer"
when issuer="B" and acquirer<>"B" and destination="B" then "B as issuer-destination"
when issuer<>"B" and acquirer="B" and destination="B" then "B as acquirer-destination"
when issuer="B" and acquirer<>"B" and destination<>"B" then "B as issuer only"
when issuer<>"B" and acquirer="B" and destination<>"B" then "B as acquirer only"
when issuer<>"B" and acquirer<>"B" and destination="B" then "B as destination only"
else "unknown"
end as bank_role
from trx_data
union all
select
issuer,acquirer,destination,
case
when issuer="C" and acquirer="C" and destination<>"C" then "C as issuer-acquirer"
when issuer="C" and acquirer<>"C" and destination="C" then "C as issuer-destination"
when issuer<>"C" and acquirer="C" and destination="C" then "C as acquirer-destination"
when issuer="C" and acquirer<>"C" and destination<>"C" then "C as issuer only"
when issuer<>"C" and acquirer="C" and destination<>"C" then "C as acquirer only"
when issuer<>"C" and acquirer<>"C" and destination="C" then "C as destination only"
else "unknown"
end as bank_role
from trx_data)zxc
group by bank_role
;

我知道这不好,有更好的方法吗?

1 回复 | 直到 7 年前

PSK 7 年前

你可以把你所有的 UNION 转换为如下所示的单个查询。

select
issuer,acquirer,destination,
case
when issuer= acquirer and issuer <> destination then  issuer + " is issuer-acquirer"
when issuer = destination and acquirer <> destination  then issuer +" as issuer-destination"
when issuer<> acquirer and acquirer= destination then acquirer + " as acquirer-destination"
when issuer<> acquirer and issuer <> destination then issuer +" as issuer only"
when issuer<>acquirer and destination <> acquirer then acquirer + " as acquirer only"
when issuer<>destination and acquirer<>destination then destination + " as destination only"
else "unknown"
end as bank_role
from trx_data

:为了处理不同的场景,我创建了一个示例,它位于SQL Server中,但它应该可以在所有数据库中工作。

select *,
case 
when issuer=t.Identifier and acquirer=t.Identifier and destination<>t.Identifier then t.Identifier +' as issuer-acquirer'
when issuer=t.Identifier and acquirer<>t.Identifier and destination=t.Identifier then t.Identifier +' as issuer-destination'
when issuer<>t.Identifier and acquirer=t.Identifier and destination=t.Identifier then t.Identifier + ' as acquirer-destination'
when issuer=t.Identifier and acquirer<>t.Identifier and destination<>t.Identifier then t.Identifier +' as issuer only'
when issuer<>t.Identifier and acquirer=t.Identifier and destination<>t.Identifier then t.Identifier + ' as acquirer only'
when issuer<>t.Identifier and acquirer<>t.Identifier and destination=t.Identifier then t.Identifier + ' as destination only'
else 'unknown'
end as bank_role


from @trx_data d
cross join
(
 select distinct issuer as 'Identifier' from @trx_data
 union 
 select distinct acquirer as 'Identifier' from @trx_data
 union 
 select distinct destination as 'Identifier' from @trx_data
)t
order by t.Identifier

Online Demo