mysql - sql - 对来自 3 列的匹配值进行分组

Question

我有一个名为 trx_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 分为:

1. value as issuer only
2. value as acquirer only
3. value as destination only
4. value as issuer & destination
5. value as issuer & acquirer
6. value as acquirer & destination

这是我的代码

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
;

我知道这不好，有什么更好的方法吗？

Answer 1

您可以将所有的 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