合并多个表的MySQL查询

Question

背景

为了为我的论文获取数据，我必须使用一个大型的、相当复杂的MySQL数据库，其中包含几个表和数百个GBs数据。不幸的是，我对SQL并不熟悉，无法真正了解如何提取我需要的数据。

数据库

数据库由几个表组成，我想将这些表组合起来。以下是其中的相关部分：

> show tables;
+---------------------------+
| Tables_in_database        |
+---------------------------+
| Build                     |
| Build_has_ModuleRevisions |
| Configuration             |
| ModuleRevisions           |
| Modules                   |
| Product                   |
| TestCase                  |
| TestCaseResult            |
+---------------------------+

表以下列方式链接在一起

Product ---(1:n)--> Configurations ---(1:n)--> Build

Build ---(1:n)--> Build_has_ModuleRevisions ---(n:1)--> ModuleRevision ---(n:1)--> Modules

Build ---(1:n)--> TestCaseResult ---(n:1)--> TestCase

表的内容如下

> describe Product;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   | UNI | NULL    |                |
+---------+--------------+------+-----+---------+----------------+


> describe Configuration;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| Product_id | int(11)      | YES  | MUL | NULL    |                |
| name       | varchar(255) | NO   | UNI | NULL    |                |
+------------+--------------+------+-----+---------+----------------+


> describe Build;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| Configuration_id | int(11)      | NO   | MUL | NULL    |                |
| build_number     | int(11)      | NO   | MUL | NULL    |                |
| build_id         | varchar(32)  | NO   | MUL | NULL    |                |
| test_status      | varchar(255) | NO   |     |         |                |
| start_time       | datetime     | YES  | MUL | NULL    |                |
| end_time         | datetime     | YES  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+


> describe Build_has_ModuleRevisions;
+-------------------+----------+------+-----+---------+----------------+
| Field             | Type     | Null | Key | Default | Extra          |
+-------------------+----------+------+-----+---------+----------------+
| id                | int(11)  | NO   | PRI | NULL    | auto_increment |
| Build_id          | int(11)  | NO   | MUL | NULL    |                |
| ModuleRevision_id | int(11)  | NO   | MUL | NULL    |                |
+-------------------+----------+------+-----+---------+----------------+


> describe ModuleRevisions;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| Module_id | int(11)      | NO   | MUL | NULL    |                |
| tag       | varchar(255) | NO   | MUL |         |                |
| revision  | varchar(255) | NO   | MUL |         |                |
+-----------+--------------+------+-----+---------+----------------+


> describe Modules;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   | UNI | NULL    |                |
+---------+--------------+------+-----+---------+----------------+


> describe TestCase;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| TestSuite_id | int(11)      | NO   | MUL | NULL    |                |
| classname    | varchar(255) | NO   | MUL | NULL    |                |
| name         | varchar(255) | NO   | MUL | NULL    |                |
| testtype     | varchar(255) | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+


> describe TestCaseResult;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| Build_id    | int(11)      | NO   | MUL | NULL    |                |
| TestCase_id | int(11)      | NO   | MUL | NULL    |                |
| status      | varchar(255) | NO   | MUL | NULL    |                |
| start_time  | datetime     | YES  | MUL | NULL    |                |
| end_time    | datetime     | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

如您所见，表与*_id字段链接。例如，TestCaseResult通过Build_id field链接到Build，通过TestCase_id字段链接到TestCase。

问题解决

现在是我的问题。给定一个特定的Configuration.name和Product.name作为输入，我需要为每个Build找到按Build.start_time排序的所有modules+revisions和失败测试用例。

我试过的

下面的查询提供给我所有Build的Configuration.name of config1和Product.name of product1

SELECT
    *
FROM
    `database`.`Build` AS b
        JOIN
    Configuration AS c ON c.id = b.Configuration_id
        JOIN
    Product as p ON p.id = c.Product_id
WHERE
    c.name = 'config1'
        AND p.name = 'product1'
ORDER BY b.start_time;

不过，这根本解决不了我一半的问题。现在，对于我需要的每一个构建

1. 查找链接到Build 的所有Build
   • 提取Modules.name字段
   • 提取ModuleRevision.revision字段

​

1. 查找所有链接到TestCase 的Build
   • 其中TestCaseResult.status = 'failure'
   • 提取链接到TestCase.name的TestCaseResult字段

​

1. 将Build与提取的模块name+revisions和测试用例名称关联起来
2. 给出由Build.start_time排序的数据，以便我可以对其进行分析。

换句话说，在所有可用的数据中，我只感兴趣地将字段Modules.name、ModuleRevision.revision、TestCaseResult.status和TestCaseResult.name链接到特定的Build，然后通过Build.start_time命令它，然后将其输出到我编写的Python程序中。

最终的结果应该类似于

Build Build.start_time    Modules+Revisions               Failed tests
    1         20140301    [(mod1, rev1), (mod2... etc]    [test1, test2, ...]
    2         20140401    [(mod1, rev2), (mod2... etc]    [test1, test2, ...]
    3         20140402    [(mod3, rev1), (mod2... etc]    [test1, test2, ...]
    4         20140403    [(mod1, rev3), (mod2... etc]    [test1, test2, ...]
    5         20140505    [(mod5, rev2), (mod2... etc]    [test1, test2, ...]

我的问题

是否有一个很好(而且效率更高)的SQL查询可以提取和显示我需要的数据？

如果没有，我完全可以提取数据的一个或几个超集/子集，以便在必要时用Python解析它。但是如何提取所需的数据呢？

Answer 1

在我看来，对此您需要多个查询。问题是Build <-> ModuleRevision和Build <- TestCaseResult的关系基本上是相互独立的。就模式而言，ModuleRevision和TestCaseResult实际上没有任何关系。你必须先查询其中一个，然后再查询另一个。您不能在一个查询中同时获得它们，因为结果中的每一行基本上表示“最深”相关表(在本例中为ModuleRevision或TestCaseResult)的一条记录，包括来自其父表的任何相关信息。因此，我认为您需要以下内容：

SELECT
    M.name, MR.revision, B.id
FROM
    ModuleRevisions MR
INNER JOIN
    Modules M ON MR.Module_id = M.id
INNER JOIN
    Build_has_ModuleRevisions BHMR ON MR.id = BHMR.ModuleRevision_id
INNER JOIN
    Build B ON BHMR.Build_id = B.id
INNER JOIN
    Configuration C ON B.Configuration_id = C.id
INNER JOIN
    Product P ON C.Product_id = P.id
WHERE C.name = 'config1' AND P.name = 'product1'
ORDER BY B.start_time;

SELECT
    TCR.status, TC.name, B.id
FROM
    TestCaseResult TCR
INNER JOIN
    TestCase TC ON TCR.TestCase_id = TC.id
INNER JOIN
    Build B ON TCR.Build_id = B.id
INNER JOIN
    Configuration C ON B.Configuration_id = C.id
INNER JOIN
    Product P ON C.Product_id = P.id
WHERE C.name = 'config1' AND P.name = 'product1' and TCR.status = 'failure'
ORDER BY B.start_time;