mysql - 使用SQL在多个变量行上进行价格搜索

我有一个低于价格表



要求每个产品在一行中有多个价格

我的SQL查询是

 select a.out_of_stack, product.*,
   ( select min(price) from temp_attr
     where product_id =product.product_id
   ) as min_price ,
   ( select max(price) from temp_attr
     where product_id = product.product_id
   ) as max_price from product
 left join users a
 on a.user_id = product.user_id
 where product.category_id in (37903,4707)
   and product.product_status in ('0')
   and product.draft=0
   and a.active=0
   and product.product_close=0
   and product.price between 10 and 4000
 group by product.product_id

使用temp_attr表使用价格进行搜索时，我需要获取产品。

最佳答案

在temp_attr中查找产品的最高/最低价格：

 SELECT MIN(price) , MAX(price) from temp_attr group by product_id

将它们与product和user表连接：

  SELECT a.out_of_stack , product.*, t1.* FROM
  product
  left join (SELECT MIN(price) , MAX(price) from temp_attr group by product_id ) as t1
  on t1.product_id = product.product_id
  left join users as a
  on a.user_id = product.user_id
  where product.category_id in (37903,4707)
    and product.product_status in ('0')
    and product.draft=0
    and a.active=0
    and product.product_close=0
    and product.price between 10 and 4000
  group by product.product_id