如何用VBA/T-SQL查询分组连续无重叠区间数据（Access转SQL Server）

我明白你的问题核心了——你需要把连续相邻且Chargeable值相同的记录合并成一个区间，而不是把所有Chargeable相同的记录不管顺序都合并到一起。原来的GROUP BY因为是全局分组，完全没考虑记录的连续性，所以把两段Yes的区间合并成了一个大区间，这显然不符合你的需求。

先把你的示例清晰展示出来：

示例输入 #

ShiftID,Name_ID,From,To,Length,Size,Chargeable
DAY1,CLASSIC_1,0.0,2.8,2.8,NQ2,Yes
DAY1,CLASSIC_1,2.8,5.8,3,NQ2,No
DAY1,CLASSIC_1,5.8,8.0,2.2,NQ2,No
DAY1,CLASSIC_1,8.0,11.1,3.1,NQ2,Yes
DAY1,CLASSIC_1,11.1,14.3,3.2,NQ2,Yes

期望输出 #

ShiftID,Name_ID,From,To,Length,Size,Chargeable
DAY1,CLASSIC_1,0.0,2.8,2.8,NQ2,Yes
DAY1,CLASSIC_1,2.8,8.0,5.2,NQ2,No
DAY1,CLASSIC_1,8.0,14.3,6.3,NQ2,Yes

原查询的问题 #

你的原查询：

SELECT ShiftID, Name_ID, Min([From]) AS [From], Max([To]) AS [To], Sum(Length) AS Length, Size, Chargeable 
FROM table1 
GROUP BY ShiftID, Name_ID, Size, Chargeable 
ORDER BY ShiftID, Name_ID, Min([From]), Max([To]), Size, Chargeable;

它会把所有Chargeable='Yes'的记录全局合并，因为GROUP BY不关心记录的顺序和相邻性，所以最终结果不符合你的连续区间需求。

方案1：SQL Server 2016 用T-SQL实现（推荐，目标库为SQL Server） #

我们可以用窗口函数精准识别连续分组：

1. 用LAG()获取上一行的Chargeable值，判断当前行和上一行是否属于同一组。
2. 用SUM() OVER()累计分组变化的标志，生成每个连续组的唯一ID。
3. 最后按这个分组ID聚合，得到连续区间的合并结果。

具体代码：

WITH GroupedRecords AS (
    SELECT 
        ShiftID,
        Name_ID,
        [From],
        [To],
        Length,
        Size,
        Chargeable,
        -- 生成分组ID：当当前行Chargeable和上一行不同时，标记为新分组
        SUM(CASE WHEN LAG(Chargeable) OVER (PARTITION BY ShiftID, Name_ID, Size ORDER BY [From]) = Chargeable THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ShiftID, Name_ID, Size ORDER BY [From]) AS GroupID
    FROM table1
)
SELECT 
    ShiftID,
    Name_ID,
    MIN([From]) AS [From],
    MAX([To]) AS [To],
    SUM(Length) AS Length,
    Size,
    Chargeable
FROM GroupedRecords
GROUP BY ShiftID, Name_ID, Size, Chargeable, GroupID
ORDER BY ShiftID, Name_ID, MIN([From]);

代码解释： #

• LAG(Chargeable) OVER (...)：在同一ShiftID、Name_ID、Size的分组里，获取上一行的Chargeable值。
• CASE WHEN ... THEN 0 ELSE 1 END：如果当前行和上一行Chargeable不同，标记为1（表示新分组开始），否则为0。
• SUM(...) OVER (...)：累计这个标记值，得到每个连续组的GroupID——同一连续组的记录会拥有相同的GroupID。
• 最后按GroupID聚合，就能得到你想要的连续区间合并结果。

方案2：Access VBA 查询（如果需要在Access中预处理） #

Access的Jet SQL不支持窗口函数，所以我们用自连接的方式生成连续分组：

SELECT 
    t1.ShiftID,
    t1.Name_ID,
    MIN(t1.[From]) AS [From],
    MAX(t2.[To]) AS [To],
    SUM(t1.Length) AS Length,
    t1.Size,
    t1.Chargeable
FROM table1 AS t1
INNER JOIN table1 AS t2 
    ON t1.ShiftID = t2.ShiftID 
    AND t1.Name_ID = t2.Name_ID 
    AND t1.Size = t2.Size 
    AND t1.Chargeable = t2.Chargeable
    AND t2.[From] >= t1.[From]
    AND NOT EXISTS (
        SELECT * 
        FROM table1 AS t3
        WHERE t3.ShiftID = t1.ShiftID 
        AND t3.Name_ID = t1.Name_ID 
        AND t3.Size = t1.Size
        AND t3.Chargeable <> t1.Chargeable
        AND t3.[From] > t1.[From] 
        AND t3.[From] < t2.[From]
    )
GROUP BY t1.ShiftID, t1.Name_ID, t1.Size, t1.Chargeable, t1.[From]
HAVING COUNT(*) > 0
ORDER BY t1.ShiftID, t1.Name_ID, MIN(t1.[From]);

代码解释： #

• 自连接t1和t2，找到所有和t1同属性组且From在t1.From之后的记录。
• NOT EXISTS子句确保t1和t2之间没有Chargeable不同的记录，保证这是一个连续无断点的区间。
• 最后按t1.From分组，合并区间和长度总和。

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