本文共 1008 字,大约阅读时间需要 3 分钟。
# 下面sql实现去重,要求是is_confirmed=1 优先 # select is_confirmed from # (select is_confirmed from product_on_sale_info # group by is_confirmed # union # select null) # as a # order by is_confirmed desc # 通过上述sql可以看到is_confirmed可能存在的三个值可以通过逆序的方式优先取第一条获得 select c.id ,c.on_sale_date ,c.product_code ,c.on_sale_type ,c.product_type ,d.brand_id ,d.brand_name ,c.on_sale_price ,c.is_confirmed ,c.is_right ,c.confirmed_by ,c.confirm_date #,product_produce_id #update_timestamp from ( select min(rid) as rid from ( select @var:=1+@var as rid,id,on_sale_date,product_code from product_on_sale_info,(select @var:=0,@var1:=0) tt #tt表仅仅是为了赋值。用于生成两个虚拟表的组号 order by on_sale_date,product_code,is_confirmed desc ) as aa group by on_sale_date,product_code ) a join ( select @var1:=1+@var1 as rid,id from product_on_sale_info order by on_sale_date,product_code,is_confirmed desc ) b on a.rid=b.rid #第二次连接排序表为了或者本身数据的id join product_on_sale_info c #获取不重复的数据 on b.id=c.id left join base_brand d on c.category_l1_name=d.category_l1_name ;转载地址:http://xlfmi.baihongyu.com/