复杂的SQL查询我无法弄清楚

I'm working on creating a report for the open source tool Validation Manager. I managed to pull out a report of all requirements covered after lots of work. I'm trying to make a report to list the ones not covered, but using not in gets the query into a never ending processing state.

我正在为开源工具Validation Manager创建报告。我设法在完成大量工作后提出了所有要求的报告。我正在尝试创建一个报告来列出未覆盖的报告,但是使用not in会使查询进入永无止境的处理状态。

Here's the DD diagram:

这是DD图:

The involved tables are in the bottom right side of the diagram. Sample data can be obtained here. What would be the correct way to get uncovered requirements?

涉及的表格位于图表的右下方。样本数据可以在这里获得。获得未覆盖的要求的正确方法是什么?

FYI: Uncovered requirements are those that doesn't have a related step and/or their children requirements are covered.

仅供参考:未披露的要求是那些没有相关步骤和/或其子女要求的要求。

The main issue is that there are, in theory, infinite number of levels of requirement relationships and the SQL I have only works for 2 levels. Trying to figure out a way to look as deep as necessary.

主要问题是理论上存在无限多级别的需求关系,而SQL我只能处理2个级别。试图找出一种尽可能深入的方式。

As reference, see the query for the covered requirements below (which is the opposite of what I need):

作为参考,请参阅下面的覆盖要求的查询(这与我需要的相反):

select
    c.covered, t.total
from
    (SELECT DISTINCT
        count(distinct unique_id) as covered
    FROM
        requirement
    WHERE
        requirement.id IN (select
                requirement.id
            from
                `requirement` requirement
            INNER JOIN `step_has_requirement` step_has_requirement ON requirement.`id` = step_has_requirement.`requirement_id`
            INNER JOIN `step` step ON step_has_requirement.`step_id` = step.`id`
                AND step.`test_case_id` = step_has_requirement.`step_test_case_id`
                AND step.`test_case_test_id` = step_has_requirement.`step_test_case_test_id`
            INNER JOIN `test_case` test_case ON step.`test_case_id` = test_case.`id`
                AND test_case.`test_id` = step.`test_case_test_id`
            INNER JOIN `test` test ON test_case.`test_id` = test.`id`
            INNER JOIN `test_plan_has_test` test_plan_has_test ON test.`id` = test_plan_has_test.`test_id`
            INNER JOIN `test_plan` test_plan ON test_plan_has_test.`test_plan_id` = test_plan.`id`
                AND test_plan.`test_project_id` = test_plan_has_test.`test_plan_test_project_id`
            INNER JOIN `test_project` test_project ON test_plan.`test_project_id` = test_project.`id`
            INNER JOIN `requirement_status` requirement_status ON requirement.`requirement_status_id` = requirement_status.`id`
            INNER JOIN `requirement_spec_node` requirement_spec_node ON requirement.`requirement_spec_node_id` = requirement_spec_node.`id`
                AND requirement_spec_node.`requirement_spec_project_id` = requirement.`requirement_spec_node_requirement_spec_project_id`
                AND requirement_spec_node.`requirement_spec_spec_level_id` = requirement.`requirement_spec_node_requirement_spec_spec_level_id`
                AND requirement_spec_node.`requirement_spec_id` = requirement.`requirement_spec_node_requirement_spec_id`
            INNER JOIN `requirement_spec` requirement_spec ON requirement_spec_node.`requirement_spec_id` = requirement_spec.`id`
                AND requirement_spec.`project_id` = requirement_spec_node.`requirement_spec_project_id`
                AND requirement_spec.`spec_level_id` = requirement_spec_node.`requirement_spec_spec_level_id`
            INNER JOIN `project` project ON requirement_spec.`project_id` = project.`id`
            WHERE
                requirement_status.status = 'general.approved'
                    and (project.id = $P{target_project_id}
                    or project.parent_project_id = $P{target_project_id}))
        or requirement.id IN (select parent_requirement_id from requirement_has_requirement where parent_requirement_id = requirement.id
and requirement_id in (select
                requirement.id
            from
                `requirement` requirement
            INNER JOIN `step_has_requirement` step_has_requirement ON requirement.`id` = step_has_requirement.`requirement_id`
            INNER JOIN `step` step ON step_has_requirement.`step_id` = step.`id`
                AND step.`test_case_id` = step_has_requirement.`step_test_case_id`
                AND step.`test_case_test_id` = step_has_requirement.`step_test_case_test_id`
            INNER JOIN `test_case` test_case ON step.`test_case_id` = test_case.`id`
                AND test_case.`test_id` = step.`test_case_test_id`
            INNER JOIN `test` test ON test_case.`test_id` = test.`id`
            INNER JOIN `test_plan_has_test` test_plan_has_test ON test.`id` = test_plan_has_test.`test_id`
            INNER JOIN `test_plan` test_plan ON test_plan_has_test.`test_plan_id` = test_plan.`id`
                AND test_plan.`test_project_id` = test_plan_has_test.`test_plan_test_project_id`
            INNER JOIN `test_project` test_project ON test_plan.`test_project_id` = test_project.`id`
            INNER JOIN `requirement_status` requirement_status ON requirement.`requirement_status_id` = requirement_status.`id`
            INNER JOIN `requirement_spec_node` requirement_spec_node ON requirement.`requirement_spec_node_id` = requirement_spec_node.`id`
                AND requirement_spec_node.`requirement_spec_project_id` = requirement.`requirement_spec_node_requirement_spec_project_id`
                AND requirement_spec_node.`requirement_spec_spec_level_id` = requirement.`requirement_spec_node_requirement_spec_spec_level_id`
                AND requirement_spec_node.`requirement_spec_id` = requirement.`requirement_spec_node_requirement_spec_id`
            INNER JOIN `requirement_spec` requirement_spec ON requirement_spec_node.`requirement_spec_id` = requirement_spec.`id`
                AND requirement_spec.`project_id` = requirement_spec_node.`requirement_spec_project_id`
                AND requirement_spec.`spec_level_id` = requirement_spec_node.`requirement_spec_spec_level_id`
            INNER JOIN `project` project ON requirement_spec.`project_id` = project.`id`
            WHERE
                requirement_status.status = 'general.approved'
                    and (project.id = $P{target_project_id}
                    or project.parent_project_id = $P{target_project_id})))
    order by unique_id) c,
    (SELECT
        count(distinct unique_id) AS total
    FROM
        `requirement_status` requirement_status
    INNER JOIN `requirement` requirement ON requirement_status.`id` = requirement.`requirement_status_id`
    INNER JOIN `requirement_spec_node` requirement_spec_node ON requirement.`requirement_spec_node_id` = requirement_spec_node.`id`
        AND requirement_spec_node.`requirement_spec_id` = requirement.`requirement_spec_node_requirement_spec_id`
    INNER JOIN `requirement_spec` requirement_spec ON requirement_spec_node.`requirement_spec_id` = requirement_spec.`id`
        AND requirement_spec.`spec_level_id` = requirement_spec_node.`requirement_spec_spec_level_id`
        AND requirement_spec.`project_id` = requirement_spec_node.`requirement_spec_project_id`
    INNER JOIN `project` project ON requirement_spec.`project_id` = project.`id`
    INNER JOIN `spec_level` spec_level ON requirement_spec.`spec_level_id` = spec_level.`id`
    WHERE
        requirement_status.status = 'general.approved'
            and (project.id = $P{target_project_id}
            or project.parent_project_id = $P{target_project_id})) t

Note: The SQL there is an example on how I managed to do the opposite, get the covered requirements. I would like to get the ones not covered. That SQl query is working properly.

注意:SQL有一个示例,说明我如何设法做相反的事情,获得​​涵盖的要求。我想得到那些没有报道的。 SQl查询工作正常。

What I am looking for is same for the parts which are not covered now(in the above)!

我正在寻找的是现在没有涵盖的部分(在上面)!

2 个解决方案

#1

First thing's first

Your schema is your biggest enemy. I have attached an SQLFiddle with your step table. It is a very impressive table, but impressive isn't always workable in SQL. You would do very well to refactor your tables and make them normalized. There are very few cases where it makes sense to put multiple integers into one text file with commas separating them. If the only thing you are going to be putting in that column, ever, is integers separated by columns, you are failing to make your SQL schema even First Normal Form. While it may seem easier to design your schema this way, it is only on the face that you are doing so. In reality, you are creating a mess of epic proportions for yourself - as you may now be discovering. By failing to even meet First Normal Form, you are failing to take advantage of any of SQL's inherent relational power.

你的架构是你最大的敌人。我已经附加了一个SQLFiddle与您的步骤表。这是一个非常令人印象深刻的表,但令人印象深刻的并不总是在SQL中可行。您可以很好地重构表并使它们标准化。很少有将多个整数放入一个文本文件中并用逗号分隔它们的情况很少。如果您将要在该列中放入的唯一内容是按列分隔的整数,那么您将无法使您的SQL架构成为第一范式。虽然以这种方式设计您的架构似乎更容易,但您只是在表面上这样做。实际上,你正在为自己创造一堆史诗般的比例 - 正如你现在可能正在发现的那样。由于未能满足First Normal Form,您无法利用SQL固有的任何关系功能。

Edit This is a fairly large schema to take in. My reaction to being not 1NF was the text part of step. I don't really know what it is aiming to do so it is hard to say for sure, but it put up a big red flag when I saw multiple rows with the same integer columns in a text box separated by commas. That usually is a sign that several columns have been concatenated into one column. After examining other parts of the schema, it is clear that other parts are normalized. It doesn't appear to be an integral part of your schema, so, my mistake. That being said this is still a very complex application with a large number of cross referencing tables. Having both requirement_has_requirement and step_has_requirement as a table may have benefits but it also has serious drawbacks.

编辑这是一个相当大的架构。我对1NF的反应是步骤的文本部分。我真的不知道它的目的是什么,所以很难肯定地说,但是当我在用逗号分隔的文本框中看到多个具有相同整数列的行时,它会竖起一个大的红旗。这通常表明多个列已连接成一列。在检查了模式的其他部分之后,很明显其他部分已经规范化。它似乎不是您的架构的组成部分,所以,我的错误。话虽如此,这仍然是一个非常复杂的应用程序,具有大量的交叉引用表。将requirement_has_requirement和step_has_requirement作为表可能有好处,但它也有严重的缺点。

I question whether you need to differentiate steps from requirements in the way you currently are. Is a requirement not just another step? I understand you need different columns, but that could be solved by having a requirement_addendum and a step_addendum table, which can be called on at need and ignored at need as well. I note that you have versioning in requirement. Do you not anticipate that you will need versions in step? Do you anticipate that some of your requirements will be versioned at different times than others? Could a version table be created to cover the three version columns in order to have just one version_id in your requirement table and related tables?

我怀疑您是否需要以当前的方式区分步骤与需求。要求不只是另一步吗?我知道你需要不同的列,但这可以通过一个requirements_addendum和一个step_addendum表来解决,该表可以根据需要调用,也可以根据需要忽略。我注意到你有要求的版本控制。您是否预计在步骤中需要版本?您是否预计您的某些要求会在不同时间进行版本控制?是否可以创建一个版本表来覆盖三个版本列,以便在需求表和相关表中只有一个version_id?

In any case, assuming you can't do anything substantial to this table and you just need to pull a query...

在任何情况下,假设你不能对这个表做任何实质性的事情,你只需要提取一个查询......

My Suggestion

You have not actually defined what "as deep as necessary" is, so you need to determine what "as deep as necessary" means. If you have a formulaic way of discovering this, you can accomplish this relatively easily by creating a recursive procedure like @RandomSeed suggested. If you do not have a particularly formulaic way of determining what "as deep as necessary" is, are you going to determine by a client-defined number of levels? If so you can still rely on a stored procedure.

你还没有真正定义“尽可能深的”,所以你需要确定“尽可能深的”意味着什么。如果你有一种公式化的方法来发现这个,你可以通过创建像@RandomSeed建议的递归过程来相对容易地完成这个。如果您没有特别公式化的方法来确定“尽可能深入”,您是否要根据客户定义的级别来确定?如果是这样,您仍然可以依赖存储过程。

If you want to make these determinations on a more case-by-case basis, or if you suspect these requirements might change, you can also consider hard-coding the information into a MySQL table which stores metadata for your schema. This can allow you to determine how many steps by running an SQL SELECT query on this metadata table to receive your answer. You can easily assign each step or each scenario to have a number of recursions you want to accomplish. You would be able to use this information in a Stored Procedure.

如果您希望在更多情况下进行这些确定,或者您怀疑这些要求可能会发生变化,您还可以考虑将信息硬编码到存储架构元数据的MySQL表中。这可以让您通过在此元数据表上运行SQL SELECT查询来确定接收答案的步数。您可以轻松地分配每个步骤或每个方案,以进行一些您想要完成的递归。您可以在存储过程中使用此信息。

If you can alter your table at all...

The benefit to altering your table even slightly is that you can create a terminal to step_has_requirement and requirement_has_requirement. Each record would then have a "yes" (1) or a "no" (0) to determine if there was a requirement involved. You could then run a query on any record which was a 1 or a 0 at your leisure.

更改表格的好处是您可以创建一个到step_has_requirement和requirement_has_requirement的终端。然后,每个记录将具有“是”(1)或“否”(0)以确定是否涉及要求。然后,您可以在闲暇时对任何1或0的记录运行查询。

requirement_has_requirement

id  | status | major_version | mid_version | minor_version
 .. |   0    |      NULL     |    NULL     |    NULL
 .. |   1    |        ..     |      ..     |     ..

step_has_requirement

id  | status | ...

Etc.

Query now becomes as simple as toggling between status 0 and status 1 to find your records. You can query all elements of your hierarchy at the same time.

查询现在变得像在状态0和状态1之间切换一样简单,以查找记录。您可以同时查询层次结构中的所有元素。

Please note that this becomes much easier and contains less repetition if step and requirement are in one table with additional columns on separate tables based on whether it was a requirement or a step. There are no doubt drawbacks to this but there are also benefits. If you have the ability to make these changes prior to Volume 1 it could be beneficial.

请注意,如果步骤和要求在一个表中,并且根据是需求还是步骤而在单独的表上添加其他列,则此操作变得更容易并且包含更少的重复。毫无疑问,这有一些缺点,但也有好处。如果您能够在第1卷之前进行这些更改,那么这可能是有益的。

Conclusion

You clearly have a very sophisticated application in development. Unfortunately the sophistication is outgrowing your schema. There is no "best" way to accomplish what you are looking for without making some minor changes to your schema. If changing your schema is not currently an option then I strongly advise you to make a quick hack so as not to waste more time and make a request to adapt the schema soon, before this becomes an even larger headache for you.

你在开发中显然有一个非常复杂的应用程序。不幸的是,复杂程度超出了你的架构。没有对您的架构进行一些细微更改,没有“最佳”方法来完成您正在寻找的内容。如果目前不能更改您的架构,那么我强烈建议您快速进行攻击,以免浪费更多时间并尽快提出调整架构的请求,然后再对您造成更大的麻烦。

#2

It's a mess, but I took a crack at it. By breaking it up into chunks with CTEs, you can troubleshoot smaller pieces.

这是一团糟,但我抓住了它。通过使用CTE将其分解为块,您可以对较小的块进行故障排除。

;WITH 

cteRequirement (id) AS
(   SELECT      distinct requirement.id
    FROM        requirement
    INNER JOIN  step_has_requirement 
        ON      requirement.id                  = step_has_requirement.requirement_id
    INNER JOIN  step 
        ON      step.id                         = step_has_requirement.step_id
        AND     step.test_case_id               = step_has_requirement.step_test_case_id
        AND     step.test_case_test_id          = step_has_requirement.step_test_case_test_id
    INNER JOIN  test_case 
        ON      test_case.id                    = step.test_case_id
        AND     test_case.test_id               = step.test_case_test_id
    INNER JOIN  test 
        ON      test.id = test_case.test_id
    INNER JOIN  test_plan_has_test 
        ON      test_plan_has_test.test_id      = test.id
    INNER JOIN  test_plan 
        ON      test_plan.id                    = test_plan_has_test.test_plan_id
        AND     test_plan.test_project_id       = test_plan_has_test.test_plan_test_project_id
    INNER JOIN  test_project 
        ON      test_project.id                 = test_plan.test_project_id
    INNER JOIN  requirement_status 
        ON      requirement_status.id           = requirement.requirement_status_id
    INNER JOIN  requirement_spec_node 
        ON      requirement_spec_node.id                                = requirement.requirement_spec_node_id
        AND     requirement_spec_node.requirement_spec_project_id       = requirement.requirement_spec_node_requirement_spec_project_id
        AND     requirement_spec_node.requirement_spec_spec_level_id    = requirement.requirement_spec_node_requirement_spec_spec_level_id
        AND     requirement_spec_node.requirement_spec_id               = requirement.requirement_spec_node_requirement_spec_id
    INNER JOIN  requirement_spec 
        ON      requirement_spec.id             = requirement_spec_node.requirement_spec_id
        AND     requirement_spec.project_id     = requirement_spec_node.requirement_spec_project_id
        AND     requirement_spec.spec_level_id  = requirement_spec_node.requirement_spec_spec_level_id
    INNER JOIN  project 
        ON      project.id                  = requirement_spec.project_id
    WHERE       requirement_status.[status] = 'general.approved'
        and     (project.id = $P{target_project_id} or project.parent_project_id = $P{target_project_id})
),

cteParent (id) AS
(   SELECT      DISTINCT parent_requirement_id
    FROM        requirement_has_requirement
    INNER JOIN  cteRequirement
        ON      cteRequirement.id = requirement_has_requirement.requirement_id
    WHERE       parent_requirement_id = requirement.id
),

ListOfRequirementIDs (requirement_id) AS
(   SELECT id FROM cteRequirement UNION
    SELECT id FROM cteParents
),

ListOfUniqueIDs (unique_id) AS
(   SELECT      DISTINCT unique_id
    FROM        requirement_status
    INNER JOIN  requirement 
        ON      requirement.requirement_status_id           = requirement_status.id
    INNER JOIN  requirement_spec_node 
        ON      requirement_spec_node.id                    = requirement.requirement_spec_node_id
        AND     requirement_spec_node.requirement_spec_id   = requirement.requirement_spec_node_requirement_spec_id
    INNER JOIN  requirement_spec 
        ON      requirement_spec_node.requirement_spec_id   = requirement_spec.id
        AND     requirement_spec.spec_level_id              = requirement_spec_node.requirement_spec_spec_level_id
        AND     requirement_spec.project_id                 = requirement_spec_node.requirement_spec_project_id
    INNER JOIN  project 
        ON      project.id                                  = requirement_spec.project_id
    INNER JOIN  spec_level 
        ON      spec_level.id                               = requirement_spec.spec_level_id
    WHERE       requirement_status.status = 'general.approved'
        and     (project.id = $P{target_project_id} or project.parent_project_id = $P{target_project_id})
)

SELECT      'Covered' AS [Type], COUNT(ListOfRequirementIDs.requirement_id) AS Cnt
FROM        ListOfRequirementIDs
UNION ALL
SELECT      'Total' AS [Type],   COUNT(ListOfUniqueIDs.unique_id) AS Cnt
FROM        ListOfUniqueIDs