时间戳

首页 > 解决方案 > MySQL查询以识别每月只有一个特定值的记录

mysql - 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

CREATE TABLE test (
  sr_number CHAR(20),
   start_time DATE NOT NULL, 
  end_time DATE, 
  renew_date DATE,  
  status CHAR(20)
);
INSERT INTO `test` 
VALUES
('s1', '2016-01-15', '2017-01-15', '2016-01-15', 'purchase'),
('s1', '2016-01-15', '2017-01-15', '2016-01-15', 'expired'),
('s2', '2016-01-15', '2017-01-15', '2016-01-15', 'purchase'),
('s2', '2016-01-15', '2017-01-15', '2016-01-15', 'expired'),
('s2', '2017-01-20', '2018-01-20', '2017-01-20', 'renew'),
('s3', '2016-01-15', '2017-01-15', '2016-01-15', 'purchase'),
('s3', '2016-01-15', '2018-01-15', '2017-01-10', 'extends');

条件：

结果应该只有一行s1, 15-01-2016, 15-01-2017, 15-01-2016, expired

示例表：https ://www.db-fiddle.com/f/bHpAAXRAVRJmkxsNDFNcwy/0

标签： mysql

解决方案

推荐阅读