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

时间:2022-07-11 15:06:23

rmb1SnatchInfoId 是商品ID,

每条记录是每个商品+不同期数period的组合=detail


需求:查询每个商品,期数<(该商品最大期数-200)的所有记录


表结构如下:

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


实现的语句:

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