如果您事先知道所有产品,可以通过像这样旋转数据来完成。
如果您不知道产品将会是什么,可以在存储过程中动态构建此查询。无论如何实现此要求,如果产品数量很大,任何一种方法的实用性都会降低,但我认为这可能是真实的。
select
product_combination,
case product_combination
when 'AAA, BBB' then aaa_bbb
when 'AAA, CCC' then aaa_ccc
when 'BBB, CCC' then bbb_ccc
when 'AAA, BBB, CCC' then aaa_bbb_ccc
end as number_of_transactions
from
(
select 'AAA, BBB' as product_combination union all
select 'AAA, CCC' union all
select 'BBB, CCC' union all
select 'AAA, BBB, CCC'
) as combination_list
cross join
(
select
sum(case when aaa = 1 and bbb = 1 then 1 else 0 end) as aaa_bbb,
sum(case when aaa = 1 and ccc = 1 then 1 else 0 end) as aaa_ccc,
sum(case when bbb = 1 and ccc = 1 then 1 else 0 end) as bbb_ccc,
sum(case when aaa = 1 and bbb = 1 and ccc = 1 then 1 else 0 end) as aaa_bbb_ccc
from
(
select
count(case when a.product_name = 'AAA' then 1 else null end) as aaa,
count(case when a.product_name = 'BBB' then 1 else null end) as bbb,
count(case when a.product_name = 'CCC' then 1 else null end) as ccc,
b.transaction_id
from
products a
inner join
transaction_has_products b
on
a.id = b.product_id
group by
b.transaction_id
) as product_matrix
) as combination_counts
结果:
product_combination number_of_transactions
AAA, BBB 2
AAA, CCC 1
BBB, CCC 1
AAA, BBB, CCC 1