MySQL子查询变量传递

发布于 2024-11-02 23:53:37 字数 523 浏览 0 评论 0原文

表：用户（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”。解决这个问题的最佳方法是什么？

原文

table: User (id INT, firstname VARCHAR)

table: Blocklist (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)))

I want to return a list of all users not the current user, say... user 1000, that isn't blocked by that user or that isn't on the blocklist of another user. Therefor it's a two-way system... you can't see people you blocked and you can see people who blocked you. Problem is, I can't very well user "theID" in the subquery. What would be the best way to tackle this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冷月断魂刀 2024-11-09 23:53:37

好的，根据示例数据，我认为这样做可以：

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

Ok, based on the sample data I think this does it:

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

If you really need to get rid of the two extra columns b1.id and 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

回复收藏 0 原文

~没有更多了~