SQL: 不在"根据*不同*行的值选择行的替代方法?

本教程将介绍SQL:“不在"根据*不同*行的值选择行的替代方法?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

SQL: 不在"根据*不同*行的值选择行的替代方法? 教程 第1张

问题描述

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?

For example:

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');

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

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');

Resulting in:

+-----------------------+----------------+
| 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!

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.

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

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

解决方案

Like this:

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;

You can also do it like this:

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:“不在"根据*不同*行的值选择行的替代方法?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。