mysql - 按 order_id 分组并在 mysql 中将 id 分配给 offer_id

Question

我有两个表 X 和表 Y。X 有 order_id，offer_id。一个 order_id 可以有多个 offer_id。现在我想将数据插入到 Y(id,offer_id) 中，对于每个不同的 offer_id 组合我都想要一个 id。例如。

表十:

order_id offer_id
2581681     24
2581681     23
2581936     23
2581936     24
2582001     23
2582001     24
2595298     24
2595298     14
2596247     24
2596247     14
2596268     14
2596268     24
1911365     1
1922052     1
1922803     1
1923501     1
1924074     1
1924963     1

表 Y 应该是这样的:

id     offer_id
1         23
1         24
2         14
2         24
3          1

Answer 1

如果你想要任意数量的报价，那么问题就相当困难了。这接近你想要的:

select (@rn := @rn + 1) id, offers
from (select group_concat(distinct x.offer_id order by x.offer_id) as offers
      from tableX x
      group by x.order_id
     ) xx cross join
     (select @rn := 0) var
group by offers;

这会将报价放在逗号分隔列表中的单行中。如果您真的需要将它们放在不同的行中，那么我会以此为基础:

select id, substring_index(substring_index(offers, ',', n.n), ',', -1) as offer
from (select (@rn := @rn + 1) id, offers
      from (select group_concat(distinct x.offer_id order by x.offer_id) as offers,
                   count(distinct x.offer_id) as numoffers
            from tableX x
            group by x.order_id
           ) xx cross join
           (select @rn := 0) var
      group by offers
     ) o join
     (select 1 as n union all select 2 as n union all select 3 as n union all select 4 as n
     ) n
     on n.n <= numoffers;

当然，n 子查询至少需要有一个订单的最大报价数。