MYSQL - 选择过去 3 个月内未输入但在过去 6 个月内输入过的现有用户
经过几个小时的搜索,我终于屈服并寻求帮助。我发现了一些让我接近的东西,但似乎无法正常工作。
基本上寻找 3-6 个月前使用过我们但最近 3 个月内没有使用过我们的用户。
为了举例,假设我有两个名为 event(id, clientFK, insert_date, officeFK) 和 client(id, name) 的表:
SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.id NOT IN (
SELECT e.id
FROM event e
WHERE e.insertion_date
BETWEEN (
NOW( ) - INTERVAL 3
MONTH
)
AND NOW( )
)
AND e.insertion_date > ( NOW( ) - INTERVAL 6
MONTH )
AND officeFK =1
ORDER BY e.insertion_date DESC
**更新*** id 只是一个自动递增列,因此显然使用上面的逻辑它永远不会显示。我的意思是在事件表中搜索 clientFK 。根据下面的第一个响应,我想出了这个:
SELECT DISTINCT e.clientFK, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
SELECT e.clientFK FROM event e
WHERE e.insertion_date > (NOW() - INTERVAL 3 DAY)
)
AND e.insertion_date BETWEEN (NOW() - INTERVAL 3 DAY) AND (NOW() - INTERVAL 6 MONTH)
AND officeFK =1
ORDER BY e.insertion_date DESC
但是,即使将 NOT IN 子查询缩小到仅 3 天,我仍然返回 0 行。将 NOT IN 更改为 IN 也会导致返回 0 行。我的事件表中有数千行,其中 clientFK 在过去 3 天“不在”。我一定是在某个地方做错了什么。
正确代码如下:
SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
SELECT e.clientFK FROM event e
WHERE e.insertion_date > (NOW() - INTERVAL 3 MONTH)
)
AND e.insertion_date < (NOW() - INTERVAL 3 MONTH)
AND insertion_date > (NOW() - INTERVAL 6 MONTH)
AND e.officeFK = 1
ORDER BY e.insertion_date DESC
After several hours of searching I'm finally giving in and asking for help. I've found something that gets me close, but doesn't seem to be working properly.
Basically looking for users who uses us between 3-6 months ago, but haven't in the last 3 months.
For the sake of the example lets say I have two tables named event(id, clientFK, insertion_date, officeFK) and client(id, name):
SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.id NOT IN (
SELECT e.id
FROM event e
WHERE e.insertion_date
BETWEEN (
NOW( ) - INTERVAL 3
MONTH
)
AND NOW( )
)
AND e.insertion_date > ( NOW( ) - INTERVAL 6
MONTH )
AND officeFK =1
ORDER BY e.insertion_date DESC
**UPDATE***
id is just an auto incrementing column so obviously it would never show up using the logic above. I meant to search for the clientFK in the event table. Based off the first response below I came up with this:
SELECT DISTINCT e.clientFK, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
SELECT e.clientFK FROM event e
WHERE e.insertion_date > (NOW() - INTERVAL 3 DAY)
)
AND e.insertion_date BETWEEN (NOW() - INTERVAL 3 DAY) AND (NOW() - INTERVAL 6 MONTH)
AND officeFK =1
ORDER BY e.insertion_date DESC
However, even when narrowing the NOT IN subquery to just 3 days I'm still returning 0 rows. Changing NOT IN to IN also results in 0 rows returned. There are thousands of rows in my events table where the clientFK is "NOT IN" the last 3 days. I've got to be doing something wrong somewhere.
CORRECT CODE BELOW:
SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
SELECT e.clientFK FROM event e
WHERE e.insertion_date > (NOW() - INTERVAL 3 MONTH)
)
AND e.insertion_date < (NOW() - INTERVAL 3 MONTH)
AND insertion_date > (NOW() - INTERVAL 6 MONTH)
AND e.officeFK = 1
ORDER BY e.insertion_date DESC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
有几种方法可以做到这一点。但我认为你已经很接近了。您的主要查询中的 3-6 个月的日期范围似乎不正确。从 6 个月到现在,您一直在检查任何内容。这与 OP 的规范不符 - 3-6 个月前的事件用户,但不是最近 3 个月内的事件。逻辑上看起来是一样的,但是试试这个。
There are several ways to do this. But I think you're close. You don't seem to have the date range correct for 3-6 months in your primary query. You were checking for anything from 6 months until now. That doesn't match the spec of the OP - users of an event 3-6 months ago, but not within the last 3 months. Logically it seems the same, but try this.