MySQL：如何在特定日期之前选择最近日期的记录

Question

假设一个简单的表格，其中包含两个用户的状态事件。 status_id为1表示使其处于“活动状态”，其他任何因素都会使其停用。 我需要找出所有那些在一年内变为的用户，例如2015-05-01（不包括该日期）。

CREATE TABLE user_status(
     user_id INT, 
     status_id INT, 
     date_assigned VARCHAR(10)); 

INSERT INTO user_status(user_id, status_id, date_assigned) 
VALUES 
(1234, 1, '2015-01-01'), -- 1234 becomes active (status id = 1) 
(1234, 2, '2015-07-01'), -- 1234 de-activated for reason 2 

(5678, 1, '2015-02-01'), -- 5678 becomes active (status id = 1) 
(5678, 3, '2015-04-01'), -- 5678 de-activated for reason 3 
(5678, 5, '2015-06-01'); -- 5678 de-activated for reason 5 

使用查询

SELECT t1.* 
FROM user_status t1 
WHERE t1.date_assigned = (SELECT MIN(t2.date_assigned)  -- the first occurrence 
          FROM user_status t2 
          WHERE t2.user_id = t1.user_id  -- for this user 
          AND t2.status_id <> 1    -- where status not active 
          AND t2.date_assigned BETWEEN  -- within 1 yr of given date 
           '2015-05-01' + INTERVAL 1 DAY -- (not including that date) 
          AND 
           '2015-05-01' + INTERVAL 1 YEAR 
         ) 

我能得到的结果

user_id status_id date_assigned 
1234  2   2015-07-01 
5678  5   2015-06-01 

这有点正确的，但用户5678不应该存在，因为虽然他们中的一个闲置事件日期范围内，它们在期望的日期范围开始之前已经不活动，因此在该范围内没有变为不活动。

我需要添加一点到我的查询中，'只显示那些有非活动事件和的用户，其中该用户的以前的status_id是1，即他们在非活动状态时处于活动状态事件发生了。

任何人都可以帮助我得到正确的语法？
见SQL fiddle

Answer 1

您可以添加NOT EXISTS到您的查询：

SELECT t1.* 
FROM user_status t1 
WHERE t1.date_assigned = (SELECT MIN(t2.date_assigned)  -- the first occurance 
          FROM user_status t2 
          WHERE t2.user_id = t1.user_id  -- for this user 
          AND t2.status_id <> 1    -- where status not active 
          AND t2.date_assigned BETWEEN  -- within 1 yr of given date 
           '2015-05-01' + INTERVAL 1 DAY -- (not including that date) 
          AND 
           '2015-05-01' + INTERVAL 1 YEAR 
         ) 
     AND NOT EXISTS (SELECT 1       -- such a record should not exist 
         FROM user_status t3 
         WHERE t3.user_id = t1.user_id -- for this user 
          AND t3.status_id <> 1  -- where status is not active 
          AND t3.date_assigned < -- before the examined period 
           '2015-05-01' + INTERVAL 1 DAY) 

Demo here

编辑：

您可以使用下面的查询也认为有多个的情况下，激活日期：

你的约会标准中找到最低（第一次改变状态）：

SELECT * 
FROM user_status 
WHERE (user_id, date_assigned) IN (
    -- get last de-activation date 
    SELECT t1.user_id, MAX(t1.date_assigned) 
    FROM user_status AS t1 
    JOIN (
    -- get last activation date 
    SELECT user_id, MAX(date_assigned) AS activation_date 
    FROM user_status 
    WHERE status_id = 1 
    GROUP BY user_id 
) AS t2 ON t1.user_id = t2.user_id AND t1.date_assigned > t2.activation_date 
    GROUP BY user_id 
    HAVING MAX(date_assigned) BETWEEN '2015-05-01' + INTERVAL 1 DAY AND '2015-05-01' + INTERVAL 1 YEAR AND 
     MIN(date_assigned) > '2015-05-01' + INTERVAL 1 DAY) 

Answer 2

自连接解决方​​案

select a.user_id,b.status_id,max(b.date_assigned) 
from user_status a 
inner join user_status b 
on a.user_id=b.user_id 
and a.date_assigned <b.date_assigned 
where b.status_id >1 and a.status_id=1 
group by a.user_id,b.status_id 
having max(b.date_assigned)> '2015-05-01' 
and max(b.date_assigned) <='2016-05-01'