非单调 SQL 查询 [英] nonmonotone SQL queries

问题描述

我是编写 SQL 查询的新手，并且被一些简单的查询所困，我需要找到 只喜欢 bud 的饮酒者

I am new to writing SQL queries and am getting stuck on some simple ones, I need to find drinkers who only like bud

对于第一个:

SELECT drinker
FROM likes
WHERE beer = 'Bud';

喜欢(饮酒者，啤酒)

我知道上面的查询将返回每个喜欢 bud 的饮酒者，我无法转换为每个只喜欢 bud 的饮酒者.

I am aware that the above query will return every drinker that likes bud, I am having trouble transitioning into every drinker that only likes bud.

推荐答案

有很多方法可以解决这个问题.我更喜欢使用 not exists 子查询，因为在这种情况下子查询不提取实际数据，因此速度很快:

There are a number of ways to solve this question. I prefer to use the one with not exists subqueries because the subquery in this case does not pull the actual data therefore it is fast:

SELECT drinker
FROM likes l1
WHERE beer = 'Bud'
AND NOT EXISTS (SELECT 1 FROM likes l2 WHERE l2.drinker=l1.drinker and l2.beer <> "Bud");

它几乎所做的是选择那些喜欢 Bud 的饮酒者，tgen 检查那些喜欢其他任何东西的人.not exists 如果没有记录匹配子查询中的条件，则返回 true.

What it pretty much does is that it selects those drinkers that like Bud and tgen checks if those like anything else. not exists returns true if no records match the condition in the subquery.

这篇关于非单调 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！