SQL:"NOT IN"基于*不同*行的值选择行的替代方法? [英] SQL: "NOT IN" alternative for selecting rows based on values of *different* rows?

问题描述

您如何制作一条SQL语句，该语句返回由子查询或联接或其他内容修改的结果，以处理您要返回的信息?

How do you make an SQL statement that returns results modified by a subquery, or a join - or something else, that deals with information you're trying to return?

例如:

CREATE TABLE bowlers (
bowling_id int4 not null primary key auto_increment,
name text,
team text
);

某人可能不正确地在一个以上的团队中:

Someone might incorrectly be on more than one team:

INSERT INTO `bowlers` (`name`, `team`) VALUES
('homer', 'pin pals'),
('moe', 'pin pals'),
('carl', 'pin pals'),
('lenny', 'pin pals'),
('homer', 'The homer team'),
('bart', 'The homer team'),
('maggie', 'The homer team'),
('lisa', 'The homer team'),
('marge', 'The homer team'),
('that weird french guy', 'The homer team');

所以homer无法决定他的球队，所以他俩都在. h！

So homer cannot decide on his team, so he's on both. Do'h!

我想认识所有正在参加the homer team团队的人，the homer team.我能做的最好的是:

I want to know everyone who is on, the homer team who is not also on the pin pals team. The best I can do is this:

SELECT a.name, a.team 
    FROM bowlers a where a.team = 'The homer team' 
    AND a.name 
    NOT IN (SELECT b.name FROM bowlers b WHERE b.team = 'pin pals');

结果:

+-----------------------+----------------+
| name                  | team           |
+-----------------------+----------------+
| bart                  | The homer team | 
| maggie                | The homer team | 
| lisa                  | The homer team | 
| marge                 | The homer team | 
| that weird french guy | The homer team | 
+-----------------------+----------------+
5 rows in set (0.00 sec)

哪个，您知道，太棒了！

Which, you know, brilliant!

性能将受到影响，因为将针对查询的每个每个结果运行子查询，即从B到A到D.数十万行.

The performance will suffer, as the subquery is going to be run for each result of the query, which is B to the A to the D. Great for a few rows, Pretty bad for the hundreds of thousands of rows.

有什么更好的方法?我主要是想通过自我加入来解决这个问题，但是我无法解决这个问题.

What is a better way? I am mostly thinking a self join would do the trick, but I can't wrap my head around how to do that.

是否有其他方法可以执行此操作，而无需使用NOT IN( SELECT ... )

Are there any other ways to do this, without using, NOT IN( SELECT ... )

此外，这种类型的问题的名字是什么?

Also, what is the name for this type of problem?

推荐答案

像这样:

SELECT a.name, a.team
FROM bowlers a
LEFT OUTER JOIN bowlers b ON a.name = b.name AND b.team = 'pin pals'
WHERE a.team = 'The homer team'
AND b.name IS NULL;

您也可以这样:

SELECT a.name, a.team
FROM bowlers a
WHERE a.team = 'The homer team'
AND NOT EXISTS (SELECT * FROM bowlers b
    WHERE b.team = 'pin pals'
    AND a.name = b.name
    );

顺便说一句，这被称为左半反连接".

By the way, this is called a "Left Anti-Semi Join".

这篇关于SQL:"NOT IN"基于*不同*行的值选择行的替代方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！