mysql kettle 分页

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