MySQL if else 在 SELECT 中不起作用

Question

我尝试执行以下查询，但没有成功。这是我第一次在 SQL 中使用 if else 语句，你能验证一下它有什么问题吗？感谢您的宝贵帮助!

SELECT 
        members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail, 
        COUNT(*) AS numROW 
        FROM members 
        WHERE members.id IN (
            SELECT (IF (friends.senderID = 9, friends.receiverID, 
                        IF (friends.senderID <> 9, friends.senderID)
                       )
                   ) 
            AS fid 
            FROM friends 
            WHERE (friends.senderID = 9 OR friends.receiverID = 9) AND friends.approved=1
            )
            )

Answer 1

完整回答。

一个主要问题是你没有分组依据。因此，如果返回多个 member.id，它们将全部汇总为一行。返回的members.id/username/mainPicture 是未定义的。这也是为什么您只返回一行的原因。

其次是你的 IF 子句。 IF 语句有 3 个参数。如果第一个为真，则返回第二个，否则返回第三个。在本例中，第三个参数是另一个 IF(这很好，您可以嵌入它们)。但该 IF 没有第三个参数。

因此，您的 SQL 可能应如下所示:-

SELECT  members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail, 
        COUNT(*) AS numROW 
FROM members 
WHERE members.id IN 
(
    SELECT IF (friends.senderID = 9, friends.receiverID, friends.senderID) AS fid 
    FROM friends 
    WHERE (friends.senderID = 9 OR friends.receiverID = 9) 
    AND friends.approved = 1
)
GROUP BY members.id AS friendID, members.username AS friendName, members.mainPicture AS thumbnail

这样做的缺点是它使用带有子查询的 IN 子句，这可能会执行得很奇怪。此外，子查询具有 2 个不同字段的 OR 关系，这将阻止它使用索引。

这应该为每个成员返回一行(假设 member.id 是唯一的)，但计数器将始终为 1。

因此，执行以下操作可能会更有效，我怀疑这可以满足您的需求

SELECT  members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail, 
        (f1.aCount + f2.aCount) AS numROW 
FROM members 
LEFT OUTER JOIN 
(
    SELECT receiverID, COUNT(*) AS aCount 
    FROM friends  
    WHERE senderID = 9 
    AND approved = 1
    GROUP BY receiverID
) f1
ON members.id = f1.receiverID 
LEFT OUTER JOIN 
(
    SELECT senderID, COUNT(*) AS aCount 
    FROM friends  
    WHERE receiverID = 9 
    AND approved = 1
    GROUP BY senderID
) f2
ON members.id = f2.senderID 

这将获取成员的所有好友记录的计数。

如果 friend 表有一个唯一的 id 字段，那么您可能会使用类似这样的内容，这应该使其能够正确使用索引:-

SELECT  members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail, 
        COUNT(DISTINCT f1.id) + COUNT(DISTINCT f2.id) AS numROW 
FROM members 
LEFT OUTER JOIN friends f1 ON members.id = f1.receiverID AND f1.senderID = 9 AND f1.approved = 1
LEFT OUTER JOIN friends f2 ON members.id = f2.senderID AND f2.receiverID = 9 AND f2.approved = 1
GROUP BY members.id AS friendID, members.username AS friendName, members.mainPicture AS thumbnail

如果您只想要没有计数的 friend 列表(重读您的评论听起来是可能的)，那么以下内容将做到这一点并且应该相当有效(每个单独的查询都可以使用索引，UNION 将排序所有重复项): -

SELECT  members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail 
FROM members 
INNER JOIN friends  
ON members.id = friends.receiverID 
WHERE senderID = 9 
AND approved = 1
UNION
SELECT  members.id AS friendID, 
        members.username AS friendName, 
        members.mainPicture AS thumbnail
FROM members 
INNER JOIN friends  
ON members.id = friends.senderID 
WHERE receiverID = 9 
AND approved = 1