小开

最佳答案

第一部分-联合和联合

这个答案包括:

使用内部连接连接两个或多个表(更多信息参见维基百科的条目)
如何使用联合查询
左右外部连接(这个stackOverflow回答非常适合描述连接类型)
相交查询(以及如何在数据库不支持的情况下重现它们)-这是SQL-Server (看到信息)的一个函数，并且首先是我写这篇文章的原因的一部分。

子查询——它们是什么，它们可以在哪里使用，以及要注意什么
笛卡尔坐标加入AKA -哦，痛苦!

有许多方法可以从数据库中的多个表中检索数据。在这个回答中，我将使用ANSI-92连接语法。这可能与其他一些使用旧的ANSI-89语法的教程不同(如果你习惯了89，可能看起来不那么直观-但我只能说尝试一下)，因为当查询开始变得更复杂时，它更容易理解多。为什么要用它?是否有性能增益?简短的回答不是，但是一旦你习惯了它，它就更容易读了。使用这种语法更容易阅读其他人编写的查询。

我还将使用一个小车库的概念，它有一个数据库来跟踪它有什么车可用。业主雇用了你作为他的IT计算机人员，并希望你能够立即向他提供他所要求的数据。

我已经制作了许多将被最终表使用的查找表。这将为我们提供一个合理的工作模型。首先，我将对一个示例数据库运行查询，该数据库具有以下结构。我将尝试思考在开始学习时常见的错误，并解释它们的错误所在，当然也会说明如何纠正它们。

第一个表是一个简单的颜色列表，以便我们知道我们的车场有什么颜色。

mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)


mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0.00 sec)

品牌表确定了不同品牌的汽车的车库可能出售。

mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)


mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)


mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> select * from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
+----+--------+
5 rows in set (0.00 sec)

模型表将覆盖不同类型的汽车，使用不同的汽车类型而不是实际的汽车模型会更简单。

mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)


mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| model | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from models;
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | 4WD    |
|  4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)

最后，把所有其他的桌子连接起来，把所有东西连接在一起的桌子。ID字段实际上是用于识别汽车的唯一批号。

mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)


mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(3) | NO   | PRI | NULL    | auto_increment |
| color | int(3) | YES  |     | NULL    |                |
| brand | int(3) | YES  |     | NULL    |                |
| model | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0


mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
|  1 |     1 |     2 |     1 |
|  2 |     3 |     1 |     2 |
|  3 |     5 |     3 |     1 |
|  4 |     4 |     4 |     2 |
|  5 |     2 |     2 |     3 |
|  6 |     3 |     5 |     4 |
|  7 |     4 |     1 |     3 |
|  8 |     2 |     2 |     1 |
|  9 |     5 |     2 |     3 |
| 10 |     4 |     5 |     1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)

这将为我们提供足够的数据(我希望)来覆盖下面不同类型的连接示例，并提供足够的数据来证明它们是有价值的。

所以说，老板想要知道他所有跑车的id。

这是一个简单的两个表连接。我们有一个标识模型的表，还有一个包含可用库存的表。如你所见，cars表的model列中的数据与我们拥有的cars表的models列相关。现在，我们知道models表的ID为1，用于Sports，因此让我们编写连接。

select
ID,
model
from
cars
join models
on model=ID

这个问题看起来不错，对吧?我们已经确定了这两个表，并包含了我们需要的信息，并使用了一个连接来正确地确定要连接的列。

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

噢,不!第一个查询出错!是的，这是一个李子。您可以看到，查询确实获得了正确的列，但其中一些列同时存在于两个表中，因此数据库会混淆我们所要表达的实际列及其位置。有两种解决方法。第一个方法很简单，我们可以使用tableName.columnName来告诉数据库我们的意思，就像这样:

select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID


+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
|  2 | Sedan  |
|  4 | Sedan  |
|  5 | 4WD    |
|  7 | 4WD    |
|  9 | 4WD    |
|  6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)

另一种可能更常用，称为表混叠。这个例子中的表有漂亮而简短的名字，但是输入像KPI_DAILY_SALES_BY_DEPARTMENT这样的名字可能很快就过时了，所以一个简单的方法是给表起这样的昵称:

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID

现在，回到请求。正如您所看到的，我们有我们需要的信息，但我们也有没有被要求的信息，因此我们需要在语句中包含一个where子句，以只获得所要求的跑车。由于我更喜欢表别名方法，而不是一遍又一遍地使用表名，我将从这一点开始坚持使用它。

显然，我们需要向查询中添加where子句。我们可以通过ID=1或model='Sports'来识别跑车。由于ID是索引和主键(而且它恰好打字较少)，所以让我们在查询中使用它。

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1


+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

宾果!老板很高兴。当然，作为一个老板，从来没有对他所要求的东西感到满意，他看着信息，然后说我也想要颜色。

好了，我们已经写好了查询的一部分，但是我们还需要用到第三个表，颜色。现在，我们的主信息表cars存储了汽车的颜色ID，它链接回colors ID列。因此，以类似于原来的方式，我们可以加入第三个表:

select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1


+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

该死，尽管表被正确连接，相关列也被链接，但我们忘记从刚刚链接的新表中拉入实际的信息。

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1


+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)

好了，老板暂时不烦我们了。现在，我要更详细地解释一下。如你所见，语句中的from子句链接了我们的主表(我经常使用包含信息的表，而不是查找表或维度表)。如果把所有的表都换了，这个查询也能正常工作，但是几个月后我们再来阅读这个查询时，它就没有那么大的意义了，所以最好尝试编写一个漂亮且易于理解的查询——直观地布局它，使用漂亮的缩进，这样一切都尽可能清晰。如果你要继续教别人，试着在他们的问题中灌输这些特征——尤其是当你要解决他们的问题时。

以这种方式链接越来越多的表是完全可能的。

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1

虽然我忘记在join语句中包含一个可能想要连接多个列的表，但下面是一个示例。如果models表有特定品牌的型号，因此也有一个名为brand的列，该列链接回ID字段的brands表，它可以这样做:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1

你可以看到，上面的查询不仅将连接的表链接到主cars表，而且还指定了已经连接的表之间的连接。如果没有这样做，结果就称为笛卡尔连接——这是dba所说的“坏”。笛卡尔连接是一种返回行，因为信息没有告诉数据库如何限制结果，因此查询返回所有符合条件的行。

因此，为了给出一个笛卡尔连接的例子，让我们运行下面的查询:

select
a.ID,
b.model
from
cars a
join models b


+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  1 | Sedan  |
|  1 | 4WD    |
|  1 | Luxury |
|  2 | Sports |
|  2 | Sedan  |
|  2 | 4WD    |
|  2 | Luxury |
|  3 | Sports |
|  3 | Sedan  |
|  3 | 4WD    |
|  3 | Luxury |
|  4 | Sports |
|  4 | Sedan  |
|  4 | 4WD    |
|  4 | Luxury |
|  5 | Sports |
|  5 | Sedan  |
|  5 | 4WD    |
|  5 | Luxury |
|  6 | Sports |
|  6 | Sedan  |
|  6 | 4WD    |
|  6 | Luxury |
|  7 | Sports |
|  7 | Sedan  |
|  7 | 4WD    |
|  7 | Luxury |
|  8 | Sports |
|  8 | Sedan  |
|  8 | 4WD    |
|  8 | Luxury |
|  9 | Sports |
|  9 | Sedan  |
|  9 | 4WD    |
|  9 | Luxury |
| 10 | Sports |
| 10 | Sedan  |
| 10 | 4WD    |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)

天啊，太丑了。然而，就数据库而言，它是被要求的完全。在查询中，我们从cars中请求ID，从models中请求model。然而，因为我们没有指定如何来连接表，数据库已经匹配了第一个表的每一个行和第二个表的每一个行。

好吧，老板回来了，他又想要更多的信息。我想要同样的列表，但也包括4wd。

然而，这给了我们一个很好的借口，看看两种不同的方式来实现这一点。我们可以像这样在where子句中添加另一个条件:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3

虽然上面的方法可以很好地工作，但让我们换个角度来看，这是展示union查询如何工作的一个很好的借口。

我们知道，以下将返回所有的跑车:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1

下面将返回所有的4wd:

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3

因此，通过在它们之间添加union all子句，第二个查询的结果将被追加到第一个查询的结果中。

select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3


+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  9 | 4WD    | Black |
+----+--------+-------+
7 rows in set (0.00 sec)

可以看到，首先返回第一个查询的结果，然后返回第二个查询的结果。

在这个例子中，简单地使用第一个查询当然会容易得多，但是union查询对于特定的情况可能很好。它们是一种很好的方法，可以从不容易连接在一起的表中返回特定的结果——或者就这个问题而言，完全不相关的表。不过，还是有一些规则要遵循的。

第一个查询中的列类型必须与下面所有其他查询中的列类型相匹配。
第一个查询中的列的名称将用于标识整个结果集。
每个查询中的列数必须相同。

现在，你可能想知道的区别是使用union和union all。union查询将删除重复项，而union all查询则不会。这确实意味着在使用union而不是union all时，会有一个小的性能损失，但结果可能是值得的——不过我不会在这里推测这类事情。

在这一点上，这里可能值得注意一些额外的注意事项。

如果我们想对结果排序，我们可以使用order by，但不能再使用别名。在上面的查询中，附加order by a.ID会导致错误——就结果而言，该列被称为ID而不是a.ID——即使在两个查询中使用了相同的别名。
我们只能有一个order by语句，并且它必须作为最后一个语句。

在下一个示例中，我将向我们的表中添加一些额外的行。

我已经将Holden添加到brands表中。我还在cars中添加了一行，它的color值为12——它在颜色表中没有引用

好吧，老板又回来了，大声要求——*我要我们经营的每个品牌的数量和汽车的数量!’——典型的情况是，我们刚讨论到一个有趣的部分，老板就想要更多的工作。

好吧，所以我们要做的第一件事就是得到一份可能的品牌的完整清单。

select
a.brand
from
brands a


+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

现在，当我们将它与我们的cars表连接起来时，我们会得到以下结果:

select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand


+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Nissan |
| Smart  |
| Toyota |
+--------+
5 rows in set (0.00 sec)

这当然是一个问题——我们没有看到任何提到我添加的可爱的Holden品牌。

这是因为连接在这两个表中查找匹配的行。由于cars中没有类型为Holden的数据，因此它不会返回。这就是我们可以使用outer连接的地方。这将返回所有一个表的结果，无论它们是否在另一个表中匹配:

select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand


+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Holden |
| Nissan |
| Smart  |
| Toyota |
+--------+
6 rows in set (0.00 sec)

现在我们有了这个，我们可以添加一个可爱的聚合函数来得到一个计数，让老板暂时离开我们。

select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand


+--------+--------------+
| brand  | countOfBrand |
+--------+--------------+
| BMW    |            2 |
| Ford   |            2 |
| Holden |            0 |
| Nissan |            1 |
| Smart  |            1 |
| Toyota |            5 |
+--------+--------------+
6 rows in set (0.00 sec)

说完，老板就偷偷溜走了。

现在，为了更详细地解释这一点，外部连接可以是left或right类型。Left或Right定义了包含完全的表。left outer join将包括左边表中的所有行，而(你猜到了)right outer join将右边表中的所有结果带入结果中。

一些数据库将允许full outer join从这两个表中返回结果(无论是否匹配)，但并非所有数据库都支持这一点。

现在，我可能认为在这个时间点上，您想知道是否可以合并查询中的连接类型-答案是肯定的，您绝对可以。

select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color


+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)

那么，为什么这不是预期的结果呢?这是因为尽管我们选择了从汽车到品牌的外部连接，但在到颜色的连接中没有指定它——因此，特定的连接只会返回在两个表中匹配的结果。

下面是可以得到我们预期结果的查询:

select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color


+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Holden | NULL  |            0 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| Toyota | NULL  |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

正如我们所看到的，查询中有两个外部连接，结果如预期的那样通过。

现在，你问的其他类型的连接呢?十字路口怎么样?

好吧，不是所有的数据库都支持intersection，但是几乎所有的数据库都允许你通过join(或者至少是一个结构良好的where语句)创建一个交集。

交集是一种连接类型，有点类似于上面描述的union -但区别在于它只有返回的数据行在由联合连接的各个单独查询之间是相同的(我的意思是相同的)。只有在各方面都相同的行才会返回。

一个简单的例子是:

select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4

虽然普通的union查询将返回表的所有行(第一个查询返回超过ID>2的任何行，第二个查询返回有ID<4的任何行)，这将导致一个完整的集合，但相交查询只返回匹配id=3的行，因为它同时满足这两个条件。

现在，如果你的数据库不支持intersect查询，上面的查询可以通过下面的查询轻松完成:

select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4


+----+-------+----------+
| ID | color | paint    |
+----+-------+----------+
|  3 | Blue  | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

如果你希望使用一个本身不支持交集查询的数据库在两个不同的表之间执行交集，你需要在表的每一列上创建一个连接。

小开

第2部分-子查询

好了，现在boss又闯进来了——我要一份我们所有这个品牌的车的清单，以及我们总共有多少辆这个品牌的车!

这是使用SQL锦囊中的下一个技巧——子查询的好机会。如果您不熟悉这个术语，子查询是在另一个查询中运行的查询。有许多不同的方法来使用它们。

对于我们的请求，让我们首先把一个简单的查询放在一起，将列出每辆车和品牌:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID

现在，如果我们想简单地获得按品牌排序的汽车数量，我们当然可以这样写:

select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand


+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

因此，我们应该能够简单地将count函数添加到原始查询中，对吧?

select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand


+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

遗憾的是，我们不能那样做。原因是，当我们添加汽车ID(列a.ID)时，我们必须将其添加到组中，所以现在，当计数函数工作时，每个ID只有一个匹配的ID。

这就是我们可以使用子查询的地方——事实上，我们可以执行两种完全不同类型的子查询，它们将返回我们需要的相同结果。第一种方法是简单地将子查询放在select子句中。这意味着每次我们获得一行数据时，子查询将运行，获得一列数据，然后将其弹出到我们的数据行中。

select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID


+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

和Bam !这对我们有好处。如果您注意到，这个子查询将必须为我们返回的每一行数据运行。即使在这个小例子中，我们只有5个不同的汽车品牌，但是子查询运行了11次，因为我们有11行数据要返回。因此，在这种情况下，这似乎不是最有效的编写代码的方式。

对于另一种方法，让我们运行子查询并假装它是一个表:

select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand


+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

好的，我们得到了相同的结果(排序略有不同——似乎数据库想要返回按我们这次选择的第一列排序的结果)，但是相同的正确数字。

那么，这两者之间的区别是什么——我们应该在什么时候使用每种类型的子查询?首先，让我们确保理解第二个查询是如何工作的。我们在查询的from子句中选择了两个表，然后编写了一个查询，并告诉数据库它实际上是一个表——数据库对此非常满意。使用这个方法有一些好处(以及一些限制)。最重要的是这个子查询运行一次。如果我们的数据库包含大量数据，那么与第一种方法相比，很可能会有巨大的改进。然而，当我们将它用作表时，我们必须引入额外的数据行——这样它们才能实际连接回我们的数据行。如果我们要像上面的查询一样使用简单的连接，我们还必须确保有足够的行数据。如果你回想一下，该连接将只拉出在连接的这两个边有匹配数据的后行。如果不小心，如果这个子查询中没有匹配的行，就可能导致cars表没有返回有效数据。

现在，回头看看第一个子查询，它也有一些限制。因为我们正在将数据拉回一行，所以我们可以只有拉回一行数据。在查询的select子句中使用的子查询通常只使用一个聚合函数，如sum， count， max或其他类似的聚合函数。它们不有 to，但通常是这样写的。

所以，在我们继续之前，让我们快速看看我们还可以在哪里使用子查询。我们可以在where子句中使用它-现在，这个例子有点做作，因为在我们的数据库中，有更好的方法来获取以下数据，但鉴于这只是一个例子，让我们看看:

select
ID,
brand
from
brands
where
brand like '%o%'


+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

这将返回一个品牌id和品牌名称的列表(第二列仅用于显示品牌)，名称中包含字母o。

现在，我们可以在where子句this中使用这个查询的结果:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like '%o%'
)


+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

可以看到，尽管子查询返回了三个品牌id，但我们的cars表只有其中两个品牌id的条目。

在这种情况下，为了进一步详细说明，子查询的工作方式就像我们编写了以下代码:

select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)


+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

同样，您可以看到子查询与手动输入在从数据库返回时如何改变行顺序。

当我们讨论子查询时，让我们看看我们还可以用子查询做什么:

您可以在另一个子查询中放置一个子查询，等等。有一个限制取决于你的数据库，但缺少一些疯狂的程序员的递归函数，大多数人永远不会达到这个限制。
你可以在一个查询中放入许多子查询，一些在select子句中，一些在from子句中，还有一些在where子句中——只要记住，你放入的每一个子查询都会使你的查询更加复杂，并且可能需要更长的执行时间。

如果您需要编写一些高效的代码，那么使用多种方式编写查询并查看(通过计时或使用解释计划)哪种查询是获得结果的最佳查询，这可能是有益的。第一种有效的方法可能并不总是最好的方法。

小开

第3部分-技巧和有效的代码

MySQL在()效率

我想我应该添加一些额外的内容，为已经出现的提示和技巧。

我看到一个相当多的问题，是如何从两个表中获得不匹配的行，我看到最普遍接受的答案如下(基于我们的汽车和品牌表- 霍尔顿被列为一个品牌，但没有出现在汽车表中):

select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)

和是的它将工作。

+----+--------+
| ID | brand  |
+----+--------+
|  6 | Holden |
+----+--------+
1 row in set (0.00 sec)

然而，在某些数据库中不是有效的。这里有一个链接到堆栈溢出问题询问它，如果你想深入了解细节，这里有一个优秀的深度文章。

简短的回答是，如果优化器不能有效地处理它，使用如下查询来获取不匹配的行可能会更好:

select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null


+--------+
| brand  |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

在子查询中用相同的表更新表

啊，另一个老的但不错的东西——老的不能在FROM子句中指定要更新的目标表'brands'。

MySQL不允许你在同一个表上运行带有子选择的update...查询。现在，你可能会想，为什么不直接把它放到where子句中呢?但是，如果你想在一堆其他行中只更新带有max()日期的行呢?你不能在where子句中这样做。

update
brands
set
brand='Holden'
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can't specify target table 'brands'
for update in FROM clause

所以，我们不能这样做，是吗?嗯，不完全是。有一个令人惊讶的大量用户不知道的鬼鬼祟祟的变通方法-尽管它确实包括一些你需要注意的黑客。

您可以将子查询插入到另一个子查询中，这将在两个查询之间留出足够的间隙，以便它能够工作。但是，请注意，将查询固定在事务中可能是最安全的—这将防止在查询运行时对表进行任何其他更改。

update
brands
set
brand='Holden'
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);


Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

小开

可以在FROM关键字中使用多个查询的概念。让我给你们看一个例子:

SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY
FROM  (
SELECT c.id cnty,l.name
FROM   county c, location l
WHERE  c.id=l.county_id AND l.end_Date IS NOT NULL
) c_loc, emp e
INNER JOIN dept d ON e.deptno =d.id
LEFT JOIN
(
SELECT l.id lappy, c.name cmpy
FROM   laptop l, company c
WHERE l.make = c.name
) lap ON e.cmpy_id=lap.cmpy

您可以使用任意数量的表。在任何必要的地方使用外部连接和联合，甚至在表子查询中也是如此。

这是一种非常简单的方法，可以涉及尽可能多的表和字段。

小开

好的，我发现这篇文章非常有趣，我想分享一些关于创建查询的知识。谢谢你的< a href = " https://stackoverflow.com/users/1450077/fluffeh " > Fluffeh < / >。其他可能读到这篇文章并觉得我错了的人有101%的自由来编辑和批评我的答案。(说实话，我很感谢你改正了我的错误。)

我将在MySQL标签中发布一些常见问题。

**技巧1 (行，匹配多个条件)**

有了这个模式

CREATE TABLE MovieList
(
ID INT,
MovieName VARCHAR(25),
CONSTRAINT ml_pk PRIMARY KEY (ID),
CONSTRAINT ml_uq UNIQUE (MovieName)
);


INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');


CREATE TABLE CategoryList
(
MovieID INT,
CategoryName VARCHAR(25),
CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);


INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');

问题

找到至少属于这两个、Comedy和Romance类别的所有的电影。

解决方案

这个问题有时会很棘手。这样的问题似乎会得到答案:-

SELECT  DISTINCT a.MovieName
FROM    MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE   b.CategoryName = 'Comedy' AND
b.CategoryName = 'Romance'

< a href = " http://sqlfiddle.com/ !2/b60cb/4" rel="noreferrer">SQLFiddle Demo .

这肯定是非常错误的，因为它会产生没有结果。对此的解释是，在每一行上只有一个有效的CategoryName值。例如，第一个条件返回真正的，第二个条件总是false。因此，通过使用AND操作符，两个条件都应该为真;否则，它就是假的。另一个问题是这样的，

SELECT  DISTINCT a.MovieName
FROM    MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')

< a href = " http://sqlfiddle.com/ !2/b60cb/2" rel="noreferrer">SQLFiddle Demo .

并且结果仍然是不正确的，因为它匹配到在categoryName上有至少的记录。真正的解决方案 是通过计算每部电影的记录实例数量吗。实例的数量应该与条件中提供的值的总数匹配。

SELECT  a.MovieName
FROM    MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

< a href = " http://sqlfiddle.com/ !2/b60cb/3" rel="noreferrer">SQLFiddle Demo(答案) .

关系型部门的SQL . SQL . SQL

**技巧2 (每个条目的最大记录)**

给定的模式,

CREATE TABLE Software
(
ID INT,
SoftwareName VARCHAR(25),
Descriptions VARCHAR(150),
CONSTRAINT sw_pk PRIMARY KEY (ID),
CONSTRAINT sw_uq UNIQUE (SoftwareName)
);


INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');


CREATE TABLE VersionList
(
SoftwareID INT,
VersionNo INT,
DateReleased DATE,
CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);


INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');

问题

查找每个软件的最新版本。显示以下列:SoftwareName，Descriptions，LatestVersion (from VersionNo列)，DateReleased

解决方案

一些SQL开发人员错误地使用MAX()聚合函数。他们倾向于这样创作，

SELECT  a.SoftwareName, a.Descriptions,
MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM    Software a
INNER JOIN VersionList b
ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

< a href = " http://sqlfiddle.com/ !2/89929/2" rel="noreferrer">SQLFiddle Demo .

(大多数RDBMS在此生成语法错误，因为没有指定group by子句上的一些未聚合的列)，结果在每个软件上产生正确的LatestVersion，但显然DateReleased是不正确的。MySQL还不支持Window Functions和Common Table Expression，而一些RDBMS已经支持了。这个问题的解决方法是创建一个subquery，它在每个软件上获得单独的最大值versionNo，然后在其他表上进行连接。

SELECT  a.SoftwareName, a.Descriptions,
b.LatestVersion, c.DateReleased
FROM    Software a
INNER JOIN
(
SELECT  SoftwareID, MAX(VersionNO) LatestVersion
FROM    VersionList
GROUP BY SoftwareID
) b ON a.ID = b.SoftwareID
INNER JOIN VersionList c
ON  c.SoftwareID = b.SoftwareID AND
c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

< a href = " http://sqlfiddle.com/ !2/89929/3" rel="noreferrer">SQLFiddle Demo(答案) .

就是这样。一旦我回忆起任何其他常见问题解答上的MySQL标签，我将发布另一个。谢谢你阅读这篇小文章。我希望你们至少能从中学到一点知识。

更新1

**技巧3 (查找两个id之间的最新记录)**

给定的模式

CREATE TABLE userList
(
ID INT,
NAME VARCHAR(20),
CONSTRAINT us_pk PRIMARY KEY (ID),
CONSTRAINT us_uq UNIQUE (NAME)
);


INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');


CREATE TABLE CONVERSATION
(
ID INT,
FROM_ID INT,
TO_ID INT,
MESSAGE VARCHAR(250),
DeliveryDate DATE
);


INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');

问题

查找两个用户之间的最新对话。

解决方案

SELECT    b.Name SenderName,
c.Name RecipientName,
a.Message,
a.DeliveryDate
FROM      Conversation a
INNER JOIN userList b
ON a.From_ID = b.ID
INNER JOIN userList c
ON a.To_ID = c.ID
WHERE     (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
SELECT  LEAST(FROM_ID, TO_ID) minFROM,
GREATEST(FROM_ID, TO_ID) maxTo,
MAX(DeliveryDate) maxDate
FROM    Conversation
GROUP BY minFROM, maxTo
)

< a href = " http://sqlfiddle.com/ !2/b7bd1/1" rel="noreferrer">SQLFiddle Demo .

小开

希望这能让它在你阅读的时候找到表格:

jsfiddle

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

SQL查询从多个表返回数据

第一部分-联合和联合

第2部分-子查询

第3部分-技巧和有效的代码

MySQL在()效率

在子查询中用相同的表更新表

技巧1 (行，匹配多个条件)

< a href = " http://sqlfiddle.com/ !2/b60cb/4" rel="noreferrer">SQLFiddle Demo .

< a href = " http://sqlfiddle.com/ !2/b60cb/2" rel="noreferrer">SQLFiddle Demo .

< a href = " http://sqlfiddle.com/ !2/b60cb/3" rel="noreferrer">SQLFiddle Demo(答案) .

技巧2 (每个条目的最大记录)

< a href = " http://sqlfiddle.com/ !2/89929/2" rel="noreferrer">SQLFiddle Demo .

< a href = " http://sqlfiddle.com/ !2/89929/3" rel="noreferrer">SQLFiddle Demo(答案) .

技巧3 (查找两个id之间的最新记录)

< a href = " http://sqlfiddle.com/ !2/b7bd1/1" rel="noreferrer">SQLFiddle Demo .

**技巧1 (行，匹配多个条件)**

**技巧2 (每个条目的最大记录)**

**技巧3 (查找两个id之间的最新记录)**