MariaDB：从一个表中的一列中选择不在另一个表的另一列子集中的字段

Update: Do not provide an answer that uses NOT EXISTS. According to MariaDB "SQL statements that use the EXISTS condition in MariaDB are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table." This query will be used a lot, so it needs to be efficient.

更新:不提供使用NOT EXISTS的答案。根据MariaDB“在MariaDB中使用EXISTS条件的SQL语句非常低效,因为子查询对于外部查询表中的每一行都是RE-RUN。”此查询将被大量使用,因此需要高效。

I have two tables following:

我有两张桌子:

CREATE TABLE `following` (
 `follower` int(1) unsigned NOT NULL,
 `followee` int(1) unsigned NOT NULL,
 PRIMARY KEY (`follower`,`followee`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and association_record:

CREATE TABLE `association_record` (
 `user_id` int(1) unsigned NOT NULL,
 `post_id` int(1) unsigned NOT NULL,
 `answer_id` int(1) unsigned NOT NULL,
 `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What I want are the followers of followee '5' who do not have an association_record with post '88'. The below SQL is what I came up with from reading other posts, but it doesn't get me the desired results:

我想要的是跟随者'5'的追随者,他们没有关联帖子'88'。下面的SQL是我从阅读其他帖子中得到的,但它没有得到我想要的结果:

select f.follower
from following f
left outer join association_record a
on f.follower = a.user_id
where f.followee = 5
and a.post_id = 88
and a.user_id is null

4 个解决方案

#1

select f.follower from following f, response_record r where f.follower = r.user_id and f.followee = 5 and r.post_id = 88 and r.user_id is null

try this

#2

To make things easier, it is better to start step by step.

为了使事情变得更容易,最好一步一步地开始。

So, we need people who follows user '5'? Here is the query.

那么,我们需要关注用户'5'的人吗?这是查询。

select follower from following where followee = 5;

Now we need to know wich users have a record on post '88'.

现在我们需要知道用户在'88'上有记录。

select user_id from association_record where post_id=88;

Now we need fixing both queries in some way. Let's do it:

现在我们需要以某种方式修复两个查询。我们开始做吧:

select follower from following where followee = 5 and not exists (select user_id from association_record where post_id=88 and user_id=follower);

And that's it. Here you have your query explained.

就是这样。在这里您解释了您的查询。

#3

Here was the query that did it:

以下是执行此操作的查询:

SELECT f.follower
FROM following f
LEFT OUTER JOIN association_record a
ON f.follower = a.user_id
AND a.poll_id = 88
WHERE f.followee = 5
AND a.user_id is null

Forgot to post the solution to my question after I solved it, and now a month later, I end up having a similar problem but without any reference to the original solution; didn't need it anymore.

在我解决之后忘了将解决方案发布到我的问题,现在一个月后,我最终遇到了类似的问题,但没有提到原始解决方案;不再需要它了。

Almost had to resolve the whole issue again from scratch; which would have been tough being that I never understood how the solution worked. Luckily, MySQL workbench keeps a log of all the queries ran from it, and trying queries to answer this question was one of the few times I've used it.

几乎不得不从头再次解决整个问题;这本来很难,因为我从未理解解决方案是如何运作的。幸运的是,MySQL工作台会记录从中运行的所有查询,并且尝试查询来回答这个问题是我使用它的少数几次之一。

Moral of the story, don't forget to post your solution; you might be doing it for yourself.

故事的道德,不要忘记发布你的解决方案;你可能会为自己做这件事。

#4

Method 2: Using join

方法2:使用join

SELECT follower
FROM following, association_record
WHERE follower=user_id
  AND followee = 5
  AND post_id=88
  AND user_id = null;

Some SQL engines use != and some use <>. Try both if you have trouble.

一些SQL引擎使用!=而一些使用<>。如果遇到麻烦,请尝试两者。

#1

select f.follower from following f, response_record r where f.follower = r.user_id and f.followee = 5 and r.post_id = 88 and r.user_id is null