Access查询：按分组规则生成D_before_G与D_after_G字段

需求说明 #

基于包含ItemID、Duration、D_before、D_after、Full_Dur字段的查询数据，需按ItemID+Duration分组生成D_before_G和D_after_G字段，规则如下：

• 若组内两条记录Full_Dur均为Yes：D_before_G直接取当前记录的D_before，D_after_G直接取当前记录的D_after
• 若组内一条记录Full_Dur为Yes、一条为No：取Full_Dur为No的那条记录的D_before和D_after作为对应字段值
• 若组内两条记录Full_Dur均为No：D_before_G取组内最小的D_before值，D_after_G取组内最小的D_after值

实现SQL代码 #

SELECT 
    t.ItemID,
    t.Duration,
    t.D_before,
    t.D_after,
    t.Full_Dur,
    -- 计算D_before_G
    IIF(
        agg.FullYesCount = 2,
        t.D_before,
        IIF(
            agg.FullYesCount = 1,
            agg.No_D_before,
            agg.Min_D_before
        )
    ) AS D_before_G,
    -- 计算D_after_G
    IIF(
        agg.FullYesCount = 2,
        t.D_after,
        IIF(
            agg.FullYesCount = 1,
            agg.No_D_after,
            agg.Min_D_after
        )
    ) AS D_after_G
FROM 
    YourOriginalQuery AS t
INNER JOIN (
    -- 分组聚合子查询，提前计算每组的关键统计值
    SELECT 
        ItemID,
        Duration,
        COUNT(IIF(Full_Dur = 'Yes', 1, NULL)) AS FullYesCount,
        MIN(D_before) AS Min_D_before,
        MIN(D_after) AS Min_D_after,
        FIRST(IIF(Full_Dur = 'No', D_before, NULL)) AS No_D_before,
        FIRST(IIF(Full_Dur = 'No', D_after, NULL)) AS No_D_after
    FROM 
        YourOriginalQuery
    GROUP BY 
        ItemID, Duration
) AS agg ON t.ItemID = agg.ItemID AND t.Duration = agg.Duration;

代码逻辑解释 #

1. 分组聚合子查询agg：
   • FullYesCount：统计每组内Full_Dur为Yes的记录数量，用来区分三种规则场景
   • Min_D_before/Min_D_after：预计算每组的最小前置/后置天数，对应规则3的取值
   • No_D_before/No_D_after：提取每组内Full_Dur为No的记录的前置/后置天数，因每组仅2条记录，用FIRST可精准获取唯一符合条件的值，对应规则2的取值
2. 主查询条件判断：
   • 当FullYesCount=2（两条都是完整时长）：直接沿用当前记录的D_before/D_after
   • 当FullYesCount=1（一条完整一条非完整）：调用子查询中预存的非完整记录的天数
   • 当FullYesCount=0（两条都是非完整）：调用子查询中预计算的最小天数

注意事项 #

请将代码中的YourOriginalQuery替换为你实际使用的查询名称或数据表名。

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