sql --- 子查询+分组函数

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)))a 
on detail.rmb1SnatchInfoId = a.id and detail.period < (a.maxPeriod - 200) and detail.status = 2 order by detail.rmb1SnatchInfoId