如果您使用的是mysql 8.0，那么可以使用窗口函数来实现这一点。它们可用于基于组生成编号：

insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  --t.Product,
  --t.Size,
  ROW_NUMBER() over (
    order by s.SortOrder
  ) partition by (t.Product) as Dropdown_menu_Position
from
  Table1 t
  inner join (
    -- Inlined now, but maybe you can make a lookup table for this
    select 'XS' as Size, 0 as SortOrder
    union all select 'S', 1
    union all select 'M', 2
    union all select 'L', 3
    union all select 'XL', 4
    union all select 'XXL', 5
  ) s on s.Size = t.Size

在mysql的早期版本中，这些函数不存在，这是行不通的。但是，对于这种特殊情况，您确实应该能够使用 count 检查当前行之前有多少行。如果您有一个单独的大小表，那么这个查询是非常可读的，但是如果没有，您必须在查询中嵌入两次查找表。下面的示例假设是一个单独的表格：

-- Assuming a lookup table like this:
create table Sizes as
  select 'XS' as Size, 0 as SortOrder
  union all select 'S', 1
  union all select 'M', 2
  union all select 'L', 3
  union all select 'XL', 4
  union all select 'XXL', 5;

-- Select all IDs and how often a smaller size exists for that product
insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  ( select count(*)
    from 
      Table1 t2
      inner join Sizes s2 on s2.Size = t2.Size 
    where
      t2.Product = t.Product and
      s2.SortOrder < s.SortOrder) as Dropdown_menu_Position
from
  Table1 t
  inner join Sizes s on s on s.Size = t.Size

但是，也许你不需要表2中的数字是连续的。也许没关系，只要他们有正确的顺序（所以m在菜单中的s下面）。当然，这取决于您的代码，但如果可能的话，您只需编写以下代码：

insert into Table2(Id, Dropdown_menu_Position)
select
  t.ID,
  s.SortOrder
from
  Table1 t
  inner join (
    -- Inlined now, but maybe you can make a lookup table for this
    select 'XS' as Size, 0 as SortOrder
    union all select 'S', 1
    union all select 'M', 2
    union all select 'L', 3
    union all select 'XL', 4
    union all select 'XXL', 5
  ) s on s.Size = t.Size