如何在SQL中仅对Auditable=Yes的工作项动作进行顺序计数？

你需要修改窗口函数的逻辑，不对所有行进行编号，只针对Auditable='Yes'的行生成连续计数，不符合条件的行返回空值。可以结合CASE条件判断和COUNT窗口函数实现：

SELECT 
    [Work Item Ref],
    [Work Item DateTime],
    Auditable,
    [Action ID],
    CASE 
        WHEN Auditable = 'Yes' 
        THEN COUNT(CASE WHEN Auditable = 'Yes' THEN 1 END) OVER (
            PARTITION BY [Work Item Ref] 
            ORDER BY [Work Item DateTime] ASC
        )
        ELSE NULL
    END AS [Action Count]
FROM 你的表名;

逻辑说明 #

• 外层CASE判断当前行是否满足Auditable='Yes'，不满足则直接返回NULL
• 满足条件时，使用COUNT窗口函数：
  • PARTITION BY [Work Item Ref]保持按工作项分区
  • ORDER BY [Work Item DateTime] ASC保证按时间顺序计数
  • 内层CASE只统计Auditable='Yes'的行，COUNT会自动忽略NULL值，实现仅对符合条件的行累计编号

当前表（原逻辑结果） #

+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
|          2500 | 19/05/2023 10:01   | Yes       |        20 |            1 |
|          2501 | 19/05/2023 10:02   | Yes       |        11 |            1 |
|          2501 | 19/05/2023 10:03   | Yes       |         9 |            2 |
|          2501 | 19/05/2023 10:04   | No        |        19 |            3 |
|          2501 | 19/05/2023 10:06   | Yes       |         5 |            4 |
|          2502 | 19/05/2023 10:04   | No        |         2 |            1 |
|          2502 | 19/05/2023 10:05   | Yes       |         4 |            2 |
+---------------+--------------------+-----------+-----------+--------------+

期望结果（新逻辑结果） #

+---------------+--------------------+-----------+-----------+--------------+
| Work Item Ref | Work Item DateTime | Auditable | Action ID | Action Count |
+---------------+--------------------+-----------+-----------+--------------+
|          2500 | 19/05/2023 10:01   | Yes       |        20 |            1 |
|          2501 | 19/05/2023 10:02   | Yes       |        11 |            1 |
|          2501 | 19/05/2023 10:03   | Yes       |         9 |            2 |
|          2501 | 19/05/2023 10:04   | No        |        19 |              |
|          2501 | 19/05/2023 10:06   | Yes       |         5 |            3 |
|          2502 | 19/05/2023 10:04   | No        |         2 |              |
|          2502 | 19/05/2023 10:05   | Yes       |         4 |            1 |
+---------------+--------------------+-----------+-----------+--------------+

内容的提问来源于stack exchange，提问作者RazorKillBen