MySQL：如何在特定日期之前选择最新日期的记录 [英] MySQL: how to select record with latest date before a certain date
问题描述
我需要在一年内找到所有成为的用户，例如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变为活动状态（状态id = 1）
（1234，2，'2015-07-01'）， - 1234因为2
（5678，1，'2015-02-01'）被取消激活， - 5678变为有效（状态id = 1）
（5678，3，'2015-04-01'）， - 5678由于3
（5678，5，2015） -06-01'）; - 5678因激活5
使用查询
SELECT t1。*
FROM user_status t1
WHERE t1.date_assigned =（SELECT MIN（t2.date_assigned） - 第一次出现
FROM user_status t2
WHERE t2.user_id = t1.user_id - 为此用户
AND t2.status_id<> 1 - 其中状态不活动
AND t2.date_assigned BETWEEN - 在给定日期的1年内
'2015-05-01'+ INTERVAL 1天 - （不包括该日期）
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小提琴
您可以向您的查询添加 NOT EXISTS
SELECT t1。*
FROM user_status t1
WHERE t1.date_assigned =（SELECT MIN（t2.date_assigned） - 第一次出现
FROM user_status t2
WHERE t2.user_id = t1.user_id - 对于此用户
AND t2.status_id<> 1 - 其中状态不活动
AND t2.date_assigned BETWEEN - 给定日期的1年内
'2015-05-01'+ INTERVAL 1天 - （不包括该日期）
AND
'2015-05-01'+ INTERVAL 1 YEAR
）
AND NOT EXISTS（SELECT 1 - 这样的记录不应该存在
FROM user_status t3
WHERE t3.user_id = t1.user_id - 对于此用户
AND t3.status_id<> 1 - 其中状态不活动
AND t3.date_assigned< - 在审查期之前
'2015-05-01'+ INTERVAL 1天）
编辑
您可以使用以下查询，也可以考虑具有多个激活日期的情况：
SELECT *
FROM user_status
WHERE（user_id，date_assigned）IN（
- get last de - 激活日期
SELECT t1.user_id，MAX（t1.date_assigned）
FROM user_status AS t1
JOIN（
- 获取最后激活日期
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）
Assume a simple table as follows containing status events for two users. A status_id of 1 makes them 'active', anything else makes them de-active. I need to find out all those users that became inactive within one year of, for example, 2015-05-01 (not including that date).
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
Using the query
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
)
I can get the result
user_id status_id date_assigned
1234 2 2015-07-01
5678 5 2015-06-01
This is sort of right but user 5678 should not be there because although they had an inactive event within the date range, they were already inactive before the desired date range began and so did not become inactive within that range.
I need to add a bit to my query along the lines of 'only show me those users who had an inactive event and where the previous status_id for that user was 1, ie they were active at the time the inactive event happened.
Can anyone help me to get the syntax correct?
See SQL fiddle
You can add NOT EXISTS to your query:
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 )
Edit:
You can use the following query that also considers the case of having multiple activation dates:
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)
这篇关于MySQL：如何在特定日期之前选择最新日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
