MySQL查询以识别每月只有一个特定值的记录

如标题中所指定,我需要编写查询以获取特定于结果的行(序列号列表),这些行每个月只有一个状态为“过期”(月数将在end_time上计算)。例如: 我的桌子是

sr_num, start_time, end_time  , renew_date, status
s1    , 15-01-2016, 15-01-2017, 15-01-2016, purchase
s1    , 15-01-2016, 15-01-2017, 15-01-2016, expired
s2    , 15-01-2016, 15-01-2017, 15-01-2016, purchase
s2    , 15-01-2016, 15-01-2017, 15-01-2016, expired
s2    , 20-01-2017, 20-01-2018, 20-01-2017, renew
s3    , 15-01-2016, 15-01-2017, 15-01-2016, purchase
s3    , 15-01-2016, 15-01-2018, 10-01-2017, extends

条件:

  • “按sr_num,end_times分组”
  • 状态只能在每个月过期

结果应该仅是一行s1,15-01-2016、15-01-2017、15-01-2016,已过期

评论