MySQL查询得到的产品有多种颜色和按颜色数量的产品订购

1 回答

• 1

  你可以使用选择形式连接表并计算不同颜色的数量

  select prod_name, count(distinct color_name)
    from ( 
  
          select 
                a.id  prod_id
              , a.name prod_name
              , b.id color_id
              , b.name color_name
          from product a
          inner join product_color c on a.id  = c.product_id
          inner join color b on b.id = c.color_id
          where b.name in ('red', 'green', 'blue')
  
    ) t 
    group by prod_name
    order by count(distinct color_name) desc
  

  回复于 2024-05-22T09:28:31+08:00