python - 结合多个表的MySQL查询

Question

背景

为了获得我论文的数据，我必须使用一个大型的、相当复杂的 MySQL 数据库，包含多个表和数百 GB 的数据数据。不幸的是，我是 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 字段链接。例如。 测试用例结果通过 Build_id 字段 链接到 Build，通过 TestCaseTestCase_id 字段。

问题描述

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

我尝试过的

以下查询为我提供了所有 Build 的 Configuration.nameconfig1 和 product1

的 Product.name

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 的 所有 Modules
   • 提取 Modules.name 字段
   • 提取 ModuleRevision.revision 字段
2. 找到 所有 TestCase链接到 Build
   • 哪里 TestCaseResult.status = 'failure'
   • 提取链接到 TestCaseResult 的 TestCase.name 字段
3. 将 Build 与提取的模块名称+修订和测试用例相关联姓名
4. 呈现按 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 s 和 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;