large-order-fusion/sql/get_fusion33.sql

with temp_member_goods as (
select
distinct s.bill_no bill_no, concat(ss.goods_id,'-', ss.color_id) as ss_gc_id
from rbp.rbp_sales_order_bill s final
left join rbp.rbp_sales_order_bill_goods sg on s.id = sg.bill_id
left join rbp.rbp_sales_order_bill_size ss on s.id = ss.bill_id and sg.id = ss.bill_goods_id
where s.bill_no in (select
s.bill_no
from rbp.rbp_sales_order_bill s final
left join rbp.rbp_sales_order_bill_goods sg on s.id = sg.bill_id
left join rbp.rbp_sales_order_bill_size ss on s.id = ss.bill_id and sg.id = ss.bill_goods_id
left join rbp.rbp_goods g on ss.goods_id = g.id
left join rbp.rbp_color cl on ss.color_id = cl.id
where g.code = 'g_code_holder' and cl.`code` = 'cl_code_holder' and sg.type = 0 and s.bill_date >= 'date_start_holder' and s.bill_date < 'date_end_holder') 
and (ss.goods_id != goods_id_holder OR ss.color_id != color_id_holder)
and s.bill_date >= 'date_start_holder' and s.bill_date < 'date_end_holder' and sg.type = 0 and sg.sales_discount > 0.5
)
SELECT
    o1.ss_gc_id AS ss_gc_id_1,
    o2.ss_gc_id AS ss_gc_id_2,
    COUNT(*) AS combination_count
FROM temp_member_goods o1
JOIN temp_member_goods o2
    ON o1.bill_no = o2.bill_no         
    AND o1.ss_gc_id < o2.ss_gc_id
GROUP BY o1.ss_gc_id, o2.ss_gc_id
ORDER BY combination_count DESC limit 0, 20;