从两个带有联接和where子句的表中获取行
从两个带有联接和where子句的表中获取行
提问于 2018-10-19 01:03:41
我需要从不等于我的用户名的“用户”表中获得用户，但也不能在阻塞的表中作为“块”。
使用只对users表进行，我正在这样做：
SELECT * FROM users WHERE username != <myself> AND interests LIKE <string>;但是，由于我已经添加了额外的‘阻止’表，我需要进一步过滤它们。
mysql> describe users;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | | NULL | |
| password | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | | NULL | |
| city | varchar(60) | NO | | NULL | |
| state | varchar(60) | NO | | NULL | |
| lat | decimal(8,6) | YES | | NULL | |
| lng | decimal(9,6) | YES | | NULL | |
| interests | text | YES | | NULL | |
| hash | varchar(32) | YES | | NULL | |
| active | int(1) | YES | | NULL | |
| avatar | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
mysql> describe blocked;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| blocker | varchar(255) | NO | | NULL | |
| blockee | varchar(255) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)我试图：
SELECT * FROM users JOIN blocked ON users.username = blocked.blocker WHERE username != 'myself' AND blocked.blockee IS NULL;伪查询如下所示：
"give me every user that's not equal to my username, but is also not a blockee where I am the blocker"因此，如果我在被阻塞的表中有以下值：
+----+-----------+----------+
| id | blocker | blockee |
+----+-----------+----------+
| 1 | myself | testuser |
+----+-----------+----------+它将返回每个人，即，而不是 testuser，并且在interests列中具有与LIKE子句相同的值。
我希望这是合理的。我卡住了。
回答 5
Stack Overflow用户
回答已采纳
发布于 2018-10-19 04:20:43
下面是一个查询，它将完成您要查找的内容：
select *
from users u
where u.username != 'myself'
and not exists (
select 1
from blocked b
where b.blocker = 'myself'
and b.blockee = u.username
)
and u.interests like '%what%';我列出了一个可以执行的示例，以查看它的行为：https://rextester.com/GPEC60793
下面是整个演示的副本：
drop table if exists demo_users;
drop table if exists demo_blocked;
create table demo_users (id int auto_increment, username varchar(255), interests text, primary key (id));
create table demo_blocked (id int auto_increment, blocker varchar(255), blockee varchar(255), primary key (id));
insert into demo_users (username, interests)
values ('myself', 'whatever'),
('testuser', 'blah'),
('somebody', 'foo'),
('nobody', 'whatfor');
insert into demo_blocked (blocker, blockee)
values ('myself', 'testuser');
select * from demo_users;
select * from demo_blocked;
select *
from demo_users u
where u.username != 'myself'
and not exists (
select 1
from demo_blocked b
where b.blocker = 'myself'
and b.blockee = u.username
)
and u.interests like '%what%';
drop table demo_users;
drop table demo_blocked;Stack Overflow用户
发布于 2018-10-19 02:04:44
此查询将提供所有非您且未被您阻止的用户的列表。然后，可以根据需要将其他条件(如AND interests LIKE <string>)添加到WHERE子句中。
SELECT u.*
FROM users u
LEFT JOIN blocked b
ON b.blockee != u.username AND b.blocker = 'myself'
WHERE u.username != 'myself' AND b.blockee IS NOT NULLStack Overflow用户
发布于 2018-10-19 01:31:02
如果您不关心性能，可以尝试sub。
SELECT * FROM users WHERE username != 'myself' and user.username not in (select locker from blocked);
我认为它对你来说是正确的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52884529
复制相关文章
相似问题
腾讯云开发者
