包含if或case以选择非活动用户的mysql查询

pod7payv  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(291)

我必须创建一个对mysql数据库的查询,这对我来说太复杂了,无法理解如何创建它。。。我需要选择1年或更长时间内处于非活动状态的所有用户。如果用户在去年添加了至少1条评论、1条请求或1条消息,则该用户处于活动状态。应该是这样的:
从regdate<now()-间隔1年的用户中选择*
还有。。。如果他有任何意见:和comment.commentdate<now()-间隔1年
还有。。。如果他有任何请求:和requests.requestdate<now()-间隔1年
还有。。。如果他有任何消息:和messages.messagedate<now()-间隔1年
但是查询将不会以这种方式工作,因为如果用户没有任何消息,但是有非常旧的请求或注解,查询将不会选择他。我想,我需要在查询中添加if()或case(),但我不知道如何添加。
示例:4个表:用户、注解、消息、请求
应选择:
usera,注册时间:2017年5月10日,无消息,无请求,2条评论,最后一条-2017年5月15日
userc,注册日期:2017年8月15日,几次请求,最后一次-2017年8月16日,几次评论,最后一次-2017年8月17日,一些消息,最后一次-2017年8月18日
不应选择:
userd,注册于2017年4月10日,他有一条2017年11月10日的评论(不超过1年)和一条2017年6月10日的消息(超过1年)
你能帮忙吗?
谢谢。
结果:我试着结合你的答案,例如:

select u.Email, uf.Phone
from User u, UserFields uf
LEFT JOIN Request r ON u.UserID = r.UserID
where u.UserID=uf.UserID
and u.TimeCreated < DATE_SUB(NOW(),INTERVAL 1 YEAR)
and u.UserID NOT IN (select r.UserID from Request as r where u.UserID=r.UserID AND r.TimeCreated >= DATE_SUB(NOW(),INTERVAL 1 YEAR))
LIMIT 0,100

这是真正的查询,我需要合并表user和userfields,我得到一个错误:1054-未知列u.userid在on子句中似乎,“on”子句是正确的。。。但为什么不起作用呢?
最终结果:我将使用以下查询:

select u.Email, uf.FirstName, uf.LastName, uf.Phone, r.TimeCreated as ReqTime
from User u
INNER JOIN UserFields uf ON u.UserID=uf.UserID
LEFT JOIN Request r ON r.UserID=u.UserID
where u.TimeCreated < DATE_SUB(NOW(),INTERVAL 1 YEAR)
GROUP BY u.Email
HAVING
    (ReqTime < DATE_SUB(NOW(),INTERVAL 1 YEAR) OR ReqTime is NULL)
LIMIT 0,100

但将在这里添加注解和消息表。我试着只要求它似乎工作得很好。。。
感谢所有帮助过你的人!

watbbzwu

watbbzwu1#

我们可以使用 not in 标准也是。
我尝试了以下查询和示例数据

select u.* from Users as u
where u.RegisterDate < DATE_SUB(NOW(),INTERVAL 1 YEAR)
and u.id not in (select c.id from Comments as c where c.comment_date >= DATE_SUB(NOW(),INTERVAL 1 YEAR))
and u.id not in (select r.id from Requests as r where r.request_date >= DATE_SUB(NOW(),INTERVAL 1 YEAR))
and u.id not in (select m.id from Messages as m where m.message_date >= DATE_SUB(NOW(),INTERVAL 1 YEAR))

对于示例数据,请使用以下查询

CREATE TABLE Users (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    RegisterDate datetime,
    PRIMARY KEY (ID)
);

CREATE TABLE Comments (
    ID int NOT NULL,
    comment varchar(255),
    comment_date datetime,
    user_id int,
    PRIMARY KEY (ID),
    FOREIGN KEY (user_id) REFERENCES Users(ID)
);
CREATE TABLE Requests (
    ID int NOT NULL,
    request varchar(255),
    request_date datetime,
    user_id int,
    PRIMARY KEY (ID),
    FOREIGN KEY (user_id) REFERENCES Users(ID)
);

CREATE TABLE Messages (
    ID int NOT NULL,
    message varchar(255),
    message_date datetime,
    user_id int,
    PRIMARY KEY (ID),
    FOREIGN KEY (user_id) REFERENCES Users(ID)
);

insert into Users(ID, Name, RegisterDate) values
(1, 'User A', STR_TO_DATE('10.05.2017', '%d.%m.%Y')),
(2, 'User B', STR_TO_DATE('11.05.2018', '%d.%m.%Y')),
(3, 'User C', STR_TO_DATE('15.08.2017', '%d.%m.%Y')),
(4, 'User D', STR_TO_DATE('10.04.2017', '%d.%m.%Y'))
;

insert into Comments (ID, user_id, comment_date) values
(1, 1, STR_TO_DATE('14.05.2017', '%d.%m.%Y')),
(2, 1, STR_TO_DATE('15.05.2017', '%d.%m.%Y')),
(3, 3, STR_TO_DATE('17.08.2017', '%d.%m.%Y')),
(4, 4, (NOW() - INTERVAL 10 MONTH))
;
insert into Requests (ID, user_id, request_date) values
(1, 3, STR_TO_DATE('16.08.2017', '%d.%m.%Y'))
;
insert into Messages (ID, user_id, message_date) values
(1, 3, STR_TO_DATE('18.08.2017', '%d.%m.%Y')),
(2, 4, STR_TO_DATE('10.06.2017', '%d.%m.%Y'))
;
sgtfey8w

sgtfey8w2#

我假设表是这样连接的:
用户1---*注解(外键:user\u id)
用户1---*请求(fk:user\u id)
用户1---*消息(fk:user\u id)
编辑:为了避免混淆,我已经完全修改了答案。
无论如何,在执行join+group by方法时,以下查询应该适用于您的情况:

select 
    u.*, 
    max(c.comment_date) 'last_comment',
    max(r.request_date) 'last_request',
    max(m.message_date) 'last_message'
from user u
left join comment c on u.id = c.user_id
left join request r on u.id = r.user_id
left join message m on u.id = m.user_id
where 
    u.register_date < NOW() - INTERVAL 1 YEAR
group by
    u.id
having
    (last_comment < NOW() - INTERVAL 1 YEAR OR last_comment is NULL)
    AND (last_request < NOW() - INTERVAL 1 YEAR OR last_request is NULL)
    AND (last_message < NOW() - INTERVAL 1 YEAR OR last_message is NULL);
6kkfgxo0

6kkfgxo03#

我想你可以使用以下来获得用户根据你的标准

SELECT * 
FROM Users u
WHERE (u.RegDate < CURRENT_DATE() -INTERVAL 1 YEAR)
AND NOT EXISTS ( SELECT 1 FROM Comments WHERE user_id = u.id AND CommentDate > CURRENT_DATE()-INTERVAL 1 YEAR)
AND NOT EXISTS ( SELECT 1 FROM Requests WHERE user_id = u.id AND RequestDate > CURRENT_DATE()-INTERVAL 1 YEAR)
AND NOT EXISTS ( SELECT 1 FROM Messages WHERE user_id = u.id AND MessageDate > CURRENT_DATE()-INTERVAL 1 YEAR)

或者通过加入你可以写成

SELECT u.id, u.name ,MAX(c.CommentDate) CommentDate ,MAX(r.RequestDate) RequestDate ,MAX(m.MessageDate) MessageDate
FROM Users u
LEFT JOIN Comments c ON u.id = c.user_id
LEFT JOIN Requests r ON u.id = r.user_id
LEFT JOIN Messages m ON u.id = m.user_id
WHERE u.RegDate  < CURRENT_DATE() - INTERVAL 1 YEAR
GROUP BY u.id, u.name
HAVING MAX(c.CommentDate) < CURRENT_DATE() - INTERVAL 1 YEAR
AND MAX(r.RequestDate) < CURRENT_DATE() - INTERVAL 1 YEAR
AND MAX(m.MessageDate) < CURRENT_DATE() - INTERVAL 1 YEAR

相关问题