gpt4 book ai didi

MySQL子查询变量传递

转载 作者:行者123 更新时间:2023-11-30 23:38:08 29 4
gpt4 key购买 nike

表:用户(id INT,名字 VARCHAR)

表:黑名单(id INT,userid INT,blocked_userid INT)

SELECT user.id theID, user.firstname
FROM user
WHERE
NOT user.id = 1000
AND IF ((SELECT id FROM blocklist WHERE theID = ANY (SELECT blocked_userid FROM blocklist WHERE userid = 1000)), 0, (IF ((1000 = ANY (SELECT userid FROM blocklist WHERE blocked_userid = theID)),0,1)))

我想返回所有用户的列表,而不是当前用户,比如...用户 1000,该用户未被该用户阻止或不在其他用户的阻止列表中。因此这是一个双向系统……你看不到你屏蔽的人,你可以看到屏蔽你的人。问题是,我不能很好地在子查询中使用“theID”。解决这个问题的最佳方法是什么?

最佳答案

好的,根据示例数据,我认为可以这样做:

SELECT u.id, u.firstname, b1.id, b2.id
FROM user AS u
JOIN user myUser ON myUser.id = 2
LEFT JOIN blocklist b1 ON u.id = b1.userid AND b1.blocked_userid = myUser.id
LEFT JOIN blocklist b2 ON u.id = b2.blocked_userid AND b2.userid = myUser.id
WHERE u.id != myUser.id
HAVING b1.id IS NULL AND b2.id IS NULL

如果你真的需要去掉 b1.id 和 b2.id 这两个额外的列:

SELECT t.theId, t.firstname FROM
(SELECT u.id as theID, u.firstname, b1.id as b1, b2.id as b2
FROM user AS u
JOIN user myUser ON myUser.id = 2
LEFT JOIN blocklist b1 ON u.id = b1.userid AND b1.blocked_userid = myUser.id
LEFT JOIN blocklist b2 ON u.id = b2.blocked_userid AND b2.userid = myUser.id
WHERE u.id != myUser.id
HAVING b1.id IS NULL AND b2.id IS NULL) as t

关于MySQL子查询变量传递,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5762434/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com