SQL查询从多个表中返回数据

2018-06-26 05:43:49

我想知道以下内容：

如何从我的数据库中的多个表中获取数据？

有什么类型的方法可以做到这一点？

什么是联合和联合，它们又有什么不同？

我应该在什么时候与其他人比较？

我打算在我的（例如PHP）应用程序中使用它，但不想针对数据库运行多个查询，我必须在单个查询中从多个表中获取数据有哪些选项？

注意：我正在写这篇文章，因为我希望能够链接到关于我在PHP队列中经常遇到的众多问题的书面指南，所以当我发布答案时，可以链接到此处以获取更多详细信息。

答案涵盖以下内容：

第1部分 - 联合和联盟

第2部分 - 子查询

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

第4部分 - 从子句中的子查询

第5部分 - 约翰伎俩的混合包

第1部分 - 联合和联盟

这个答案包括：

第1部分

使用内部连接加入两个或多个表格（请参阅维基百科条目了解更多信息）

如何使用联合查询

左外右连接（这个stackOverflow的答案非常适合描述连接的类型）

相交查询（以及如果数据库不支持它们时如何重现它们） - 这是SQL-Server的一个功能（请参阅info），也是我首先编写整个事件的部分原因。

第2部分

子查询 - 它们是什么，它们可以在哪里使用以及需要注意什么

笛卡儿加入AKA - 哦，不幸的是！

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

我也将使用一个小型caryard的概念，它有一个数据库来跟踪它可用的汽车。老板已经雇佣了你作为他的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)

品牌表标识了caryard可能销售的不同汽车品牌。

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。

这是一个简单的两表连接。我们有一个表格，用于确定模型和表格中的可用库存。正如你所看到的，在数据model中的列cars表涉及models的列cars表，我们有。现在，我们知道模型表的Sports 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，并将其链接回颜色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和model的models 。但是，因为我们没有指定如何连接表，所以数据库将第一个表中的每一行与第二个表中的每一行进行匹配。

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

然而，这给了我们一个很好的借口来看待两种不同的方式来实现这一点。我们可以在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

以下内容将返回所有四驱车：

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 all使用union时候会有小的性能损失，但结果可能是值得的 - 我不会在这方面进行推测。

在此注意，这里可能值得注意一些附加说明。

如果我们想要订购结果，我们可以使用order by但不能再使用别名。在上面的查询中， order by a.ID附加order by a.ID会导致错误 - 就结果而言，该列被称为ID而不是a.ID - 即使在两个查询中都使用了相同的别名。

我们只能order by声明发出一个order by ，并且必须作为最后的声明。

对于下面的例子，我在表中添加了一些额外的行。

我已将Holden添加到品牌表中。我还在cars中添加了一行color值为12 - 在颜色表中没有参考。

好吧，老板又回来了，吼出要求 - *我想要计算我们携带的每个品牌以及它里面的汽车数量！` - 典型的，我们只是讨论一个有趣的部分，老板想要更多的工作。

Rightyo，所以我们需要做的第一件事就是获得可能品牌的完整列表。

select
    a.brand
from
    brands a

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

现在，当我们将其加入我们的汽车表格时，我们得到以下结果：

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品牌。

这是因为联接在两个表中查找匹配的行。由于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 outer join将包括左侧表中的所有行，而（您猜对此） right 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但几乎所有的数据库都允许你通过一个连接创建一个交集（或者至少是一个结构合理的where语句）。

交集是一种类似于上述union的连接 - 但区别在于它只返回联合加入的各个单独查询之间相同的数据行（并且我的意思是相同的）。只有在各方面相同的行才会被返回。

一个简单的例子就是这样：

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

虽然普通union查询会返回表的所有行（第一个查询返回ID>2的任何内容，第二个ID<4任何内容），这会导致整个集合，但相交查询只会返回匹配id=3的行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)

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

好的，我发现这篇文章非常有趣，我想分享一些关于创建查询的知识。感谢Fluffeh 。其他可能阅读本文并可能认为我错了的人可以免费编辑并批评我的答案。（老实说，我很感激纠正我的错误。）

我将在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'

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 ， Descriptions ， LatestVersion （来自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

SQLFiddle演示

（因为没有在group by子句中指定某些非聚合列，所以大多数RDBMS都会生成语法错误），结果会在每个软件上生成正确的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

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演示

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

那么，我们应该能够简单地将计数函数添加到我们原始的查询权限中？

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)

而巴姆！，这会做我们。如果你注意到，这个子查询将不得不为我们返回的每一行数据运行。即使在这个小例子中，我们只有五种不同的汽车品牌，但子查询运行了十一次，因为我们有十一行数据正在返回。因此，在这种情况下，它似乎不是编写代码的最有效方式。

对于不同的方法，让我们运行一个子查询并假装它是一个表格：

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或其他类似的聚合函数。他们不需要，但通常他们是如何写的。

所以，在我们继续之前，让我们快速看看我们还可以使用子查询。我们可以在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子句中使用这个查询的结果：

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，我们的汽车表格也只有其中两个的条目。

在这种情况下，为了得到更多的细节，子查询就像我们写下如下代码一样工作：

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

如果您需要编写一些高效的代码，以多种方式编写查询并查看（通过计时或通过使用解释计划）是最佳查询来获得结果的做法会很有帮助。工作的第一种方式可能并不总是最好的方式。

链接地址: http://www.djcxy.com/p/73397.html

上一篇: SQL query return data from multiple tables

下一篇: Create new user in MySQL and give it full access to one database