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

斯菲德尔

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

第一部分-联合和联合

这个答案包括:

Part 1 Joining two or more tables using an inner join (See the wikipedia entry for additional info) How to use a union query Left and Right Outer Joins (this stackOverflow answer is excellent to describe types of joins) Intersect queries (and how to reproduce them if your database doesn't support them) - this is a function of SQL-Server (see info) and part of the reason I wrote this whole thing in the first place. Part 2 Subqueries - what they are, where they can be used and what to watch out for Cartesian joins AKA - Oh, the misery!

There are a number of ways to retrieve data from multiple tables in a database. In this answer, I will be using ANSI-92 join syntax. This may be different to a number of other tutorials out there which use the older ANSI-89 syntax (and if you are used to 89, may seem much less intuitive - but all I can say is to try it) as it is much easier to understand when the queries start getting more complex. Why use it? Is there a performance gain? The short answer is no, but it is easier to read once you get used to it. It is easier to read queries written by other folks using this syntax.

我还将使用一个小车库的概念，它有一个数据库来跟踪它有什么车可用。业主雇用了你作为他的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)

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

言下之意，老板想知道他所有跑车的编号。

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

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)

Right, that's the boss off our back for a moment. Now, to explain some of this in a little more detail. As you can see, the from clause in our statement links our main table (I often use a table that contains information rather than a lookup or dimension table. The query would work just as well with the tables all switched around, but make less sense when we come back to this query to read it in a few months time, so it is often best to try to write a query that will be nice and easy to understand - lay it out intuitively, use nice indenting so that everything is as clear as it can be. If you go on to teach others, try to instill these characteristics in their queries - especially if you will be troubleshooting them.

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

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语句中包含一个可能需要连接多个列的表，但这里有一个示例。如果模型表有品牌特定的模型，因此也有一个名为brand的列，它链接回ID字段的品牌表，它可以这样做:

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)

天啊，太丑了。然而，就数据库而言，这正是我们所需要的。在查询中，我们从汽车中查询ID，从模型中查询模型。但是，由于我们没有指定如何连接表，数据库已经匹配了第一个表中的每一行和第二个表中的每一行。

好吧，老板回来了，他又想要更多的信息。我想要同样的列表，但也包括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

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

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

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

因此，通过在它们之间添加一个联合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 all之间有什么区别。联合查询将删除重复项，而联合查询则不会。这确实意味着使用联合而不是联合的时候会有一个小的性能损失，但结果可能是值得的——不过我不会在这方面进行推测。

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

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

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

我已经在品牌表中添加了霍顿。 我还在cars中添加了一个颜色值为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)

这当然是一个问题-我们没有看到任何提及可爱的霍顿品牌我补充。

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

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定义了完全包含哪个表。左外连接将包括左边表中的所有行，而右外连接(您已经猜到了)将右边表中的所有结果带入结果。

有些数据库允许使用完整的外部连接，从两个表中返回结果(无论是否匹配)，但并非所有数据库都支持这种连接。

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

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)

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

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

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

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

一个简单的例子是:

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

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

现在，如果你的数据库不支持交叉查询，上面的查询可以很容易地完成以下查询:

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)

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

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

斯菲德尔

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

第2部分-子查询

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

这是使用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)

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

So, what's the difference between the two - and when should we use each type of subquery? First, lets make sure we understand how that second query works. We selected two tables in the from clause of our query, and then wrote a query and told the database that it was in fact a table instead - which the database is perfectly happy with. There can be some benefits to using this method (as well as some limitations). Foremost is that this subquery ran once. If our database contained a large volume of data, there could well be a massive improvement over the first method. However, as we are using this as a table, we have to bring in extra rows of data - so that they can actually be joined back to our rows of data. We also have to be sure that there are enough rows of data if we are going to use a simple join like in the query above. If you recall, the join will only pull back rows that have matching data on both sides of the join. If we aren't careful, this could result in valid data not being returned from our cars table if there wasn't a matching row in this subquery.

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

所以，在我们继续之前，让我们快速看看我们还可以在哪里使用子查询。我们可以在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)

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

现在，我们可以在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子句中—只需记住，您放入的每一个子查询都会使您的查询更加复杂，并且可能需要更长的执行时间。

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

可以在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

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

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

好的，我发现这篇文章非常有趣，我想分享一些关于创建查询的知识。谢谢你的绒毛。其他可能读到这篇文章并觉得我错了的人有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');

问题

找到所有至少属于喜剧和爱情类的电影。

解决方案

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

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

SQLFiddle演示

这绝对是错误的，因为它不会产生任何结果。对此的解释是，每行上只有一个有效的CategoryName值。例如，第一个条件返回true，第二个条件总是false。因此，通过使用AND运算符，两个条件都应为真;否则，它就是假的。另一个问题是这样的，

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

SQLFiddle演示

结果仍然是不正确的，因为它匹配到在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

SQLFiddle演示(答案)

关系型划分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, description,LatestVersion (from VersionNo列)，daterreleased

解决方案

一些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

SQLFiddle演示

(大多数RDBMS在此生成语法错误，因为没有指定group by子句上的一些未聚合的列)结果在每个软件上生成正确的LatestVersion，但显然daterreleased是不正确的。MySQL不像一些RDBMS那样支持窗口函数和公共表表达式。解决这个问题的方法是创建一个子查询，它在每个软件上获得单个的最大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

SQLFiddle演示(答案)

---

就是这样。一旦我想起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
)

SQLFiddle演示