sql - Access SQL - 连续数周失败

Question

我在 Access 中有一个表，其中记录了每个人每天的测试结果。有些人可能在同一天参加了不止一项考试。我已经展示了下表的简化版本:

|  ID | testDate  |  Person   |   Pass? | ConsecFailDays |
----------------------------------------------------------
|  01 |  01/08/18 |  John     |  Fail   |                |
|  02 |  01/08/18 |  John     |  Pass   |                |
|  03 |  03/08/18 |  John     |  Fail   |                |
|  04 |  01/08/18 |  Mark     |  Fail   |                |
|  05 |  02/08/18 |  Mark     |  Pass   |                |

我尝试编写一个 SQL UPDATE 查询，该查询将使用该人至少通过一次测试失败的连续天数更新最后一列(不一定是连续的日历天数，只是他们实际进行测试的天数)。最终的结果应该是这样的......

|  ID | testDate  |  Person   |   Pass? | ConsecFailDays |
----------------------------------------------------------
|  01 |  01/08/18 |  John     |  Fail   |       1        |
|  02 |  01/08/18 |  John     |  Pass   |       1        |
|  03 |  03/08/18 |  John     |  Fail   |       2        |
|  04 |  01/08/18 |  Mark     |  Fail   |       1        |
|  05 |  02/08/18 |  Mark     |  Pass   |       0        |

我真的很难让它工作，最后我求助于使用 VBA 来创建每个唯一人的记录集，然后每天循环让那个人检查他们当天是否失败。我的数据集非常大，运行需要几个小时。

我希望对整个数据集进行操作的查询会快得多。有谁知道是否有 SQL 解决方案呢？

Answer 1

您需要多个子查询来获取通过测试且没有失败的最后日期，然后计算此日期和 testDate 之间的天数:

SELECT t1.ID
    ,t1.testDate
    ,t1.Person
    ,t1.[Pass?]
    ,(
        SELECT count(testDate)
        FROM (
            SELECT DISTINCT testDate
                ,Person
                ,[Pass?]
            FROM yourTable
            ) AS t2
        WHERE t2.[Pass?] = false
            AND t2.Person = t1.Person
            AND t2.testDate >= Nz((
                            SELECT max(t3.testDate)
                            FROM (
                                SELECT t4.testDate
                                    ,t4.Person
                                FROM yourTable AS t4
                                WHERE (
                                        ((t4.[Pass?]) = True)
                                        AND (
                                            (
                                                (
                                                    SELECT count(*) AS failed
                                                    FROM yourTable AS t5
                                                    WHERE t5.testDate = t4.testDate
                                                        AND t5.Person = t4.Person
                                                        AND t5.[Pass?] = false
                                                    )
                                                ) = 0
                                            )
                                        )
                                ) AS t3
                            WHERE t1.Person = t3.Person
                                AND t3.testDate <= [t1].[testDate]
                            GROUP BY Person
                            ))
                AND t2.testDate <= t1.testDate
        ) AS ConsecFailDays
FROM yourTable AS t1;

其中 t2 计算不同的天数(如果每天只有一次失败可以加快速度，您可以删除 DISTINCT)。

t3 是测试通过且没有失败的日子。

t4 是通过测试(也可能是测试失败)的日子。

t5 计算当天测试通过的失败测试。

如您所愿 Update-Query您可以使用:

UPDATE yourTable SET ConsecFailDays = DLookUp("ConsecFailDays","newQuery","ID = " & yourTable.ID)

但你应该试试 Select-Query首先是索引。如果性能太差，您可以使用更新，但每次数据更改时都必须运行它。

意见建议:

不要使用像 ? 这样的特殊字符在 Pass?对于列名或表名，以避免被迫使用方括号。

人应该是表人的外键，因为人可以有相同的名字(例如约翰史密斯)。

[Pass?] 应该是 bool 值(真/假)。如果你想留在字符串上，你必须替换 [Pass?] = false与 [Pass?] = 'Fail'和 [Pass?] = true和[Pass?] = 'Pass'

应该有 testDate 的索引, Person以及 testDate, Person 的组合索引以提高查询性能。