rmb1SnatchInfoId 是商品ID,
每条记录是每个商品+不同期数period的组合=detail
需求:查询每个商品,期数<(该商品最大期数-200)的所有记录
表结构如下:
实现的语句:
select detail.id,detail.period,detail.rmb1SnatchInfoId from web_1rmb_snatch_detail detail
join (select rmb1SnatchInfoId as 'id',period as 'period',max(period) as 'maxPeriod' from web_1rmb_snatch_detail where status = 2 group by rmb1SnatchInfoId having (period < (max(period) - 200)))aon detail.rmb1SnatchInfoId = a.id and detail.period < (a.maxPeriod - 200) and detail.status = 2 order by detail.rmb1SnatchInfoId