mysql - 在 MYSQL 查询中使用 If

Question

您好，我目前正在编写一些代码，在从数据库中检索结果之前需要一个 if。

情况是，如果 interacterID 不为 NULL，则按它进行过滤，见下文

SELECT *
FROM status
WHERE type != "3"
AND userID = "'.$userID.'"
AND 
(
    interacterID IN ( #IF interacterID is null I want to skip this AND
        SELECT userID
        FROM userFriends
        WHERE (
            userFriends.userID = "'.$currentUserID.'"
            OR userFriends.interacterID = "'.$currentUserID.'"
        )
        AND status = "1"
    )
)
ORDER BY addedDate DESC
LIMIT 1

在此先感谢您的帮助。

Answer 1

如果我没看错，我不确定您是否需要此处的 IF ( which can be done )。一个简单的 IS NULL 和 OR 可以用于这两种情况:

SELECT *
FROM status
WHERE type != "3"
AND userID = "'.$userID.'"
AND 
(
    interacterID IS NULL OR interacterID IN (
        SELECT userID
        FROM userFriends
        WHERE (
            userFriends.userID = "'.$currentUserID.'"
            OR userFriends.interacterID = "'.$currentUserID.'"
        )
        AND status = "1"
    )
)
ORDER BY addedDate DESC
LIMIT 1