# 下面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
;
# 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
;