基于分组和顺序的求和

Question

我有如下的原始数据..。

WO  OP  WC          Time
1   10  Band Saw    2.0
1   15  Band Saw    5.0
1   17  Band Saw    10.0
1   20  CNC Lathe   6.0
1   22  Band Saw    102.0
1   30  Inspection  33.0
2   10  Band Saw    1.5
2   20  CNC Lathe   6.00
2   20  CNC Lathe   2.00
2   30  CNC Punch   0.5
2   40  Manual Ops  1.25
2   50  Inspection  0.00

我需要像下面这样的求和和分组。即总结WO/WC列的时间，直到WC发生变化，而不是整个WO的总时间。希望我已经解释清楚(可能不是)，我们需要支持Server 2005。

WO  WC          Time
1   Band Saw    17.0
1   CNC Lathe   6.0
1   Band Saw    102.0
1   Inspection  33.0
2   Band Saw    1.5
2   CNC Lathe   8.00
2   CNC Punch   0.5
2   Manual Ops  1.25
2   Inspection  0.00

Answer 1

像这样的东西呢。我在您定义的顺序(WO和OP)上构建了一个row_number，然后，我在按WO和OP排序的每个WC上构建了另一个row_number。现在您有两个row_numbers。一套一套，每个WC一份。现在，当从前者中减去后者时，在只有1行差的地方形成组，每次有MORe而不是一行时，就会得到一个新的分组。不需要递归。

;with t (WO, OP, WC, Time) as
(
select 1,   10,  'Band Saw',    2.0
union all select 1,   15,  'Band Saw',    5.0
union all select 1,   17,  'Band Saw',    10.0
union all select 1,   20,  'CNC Lathe',   6.0
union all select 1,   22,  'Band Saw',    102.0
union all select 1,   30,  'Inspection',  33.0
union all select 2,   10,  'Band Saw',    1.5
union all select 2,   20,  'CNC Lathe',   6.00
union all select 2,   20,  'CNC Lathe',   2.00
union all select 2,   30,  'CNC Punch',   0.5
union all select 2,   40,  'Manual Ops',  1.25
union all select 2,   50,  'Inspection',  0.00
), rn as
(

    select 
        grp= row_number() over (order by WO, op) - row_number() over (partition by wo, wc order by wo, op),
        *
    from t
)
select grp, wo, wc, sum(time)
from rn
group by grp, wo, wc

编辑以使用一个cte。还将分区按第二阶修正。这在提供的数据方面优于rcte，并且可能会更好地扩展，因为它不需要递归任意次数。

Answer 2

使用递归CTE：

;WITH
    cte1 AS
    (
        SELECT  WO, OP, WC, Time,
                ROW_NUMBER() OVER (ORDER BY WO, Op) AS RowNumber
        FROM    MyTable
    ),
    cte2 AS
    (
        SELECT  WO, OP, WC, Time, RowNumber,
                1 AS GroupID
        FROM    cte1
        WHERE   RowNumber = 1
        UNION ALL
        SELECT  cte1.WO, cte1.OP, cte1.WC, cte1.Time, cte1.RowNumber,
                CASE
                    WHEN cte1.WC = cte2.WC THEN cte2.GroupID
                    ELSE cte2.GroupID + 1
                END AS GroupID
        FROM    cte1
        INNER JOIN cte2 ON cte1.RowNumber = cte2.RowNumber + 1  
    )

SELECT  WO, WC, SUM(Time) As TotalTime
FROM    cte2
GROUP BY GroupID, WO, WC
OPTION  (MAXRECURSION 0)

我没有SQL Server 2005可供测试，但它应该可以工作。如果需要更多详细信息，请查询cte1和cte2。

Answer 3

感谢Xedni，他给了我大部分的答案。我必须将Max(op)和排序添加到他提供的解决方案中。

;with t (WO, OP, WC, Time) as
    (
    select 1,   10,  'Band Saw',    2.0
    union all select 1,   15,  'Band Saw',    5.0
    union all select 1,   17,  'Band Saw',    10.0
    union all select 1,   20,  'CNC Lathe',   6.0
    union all select 1,   22,  'Band Saw',    102.0
    union all select 1,   30,  'Inspection',  33.0
    union all select 2,   10,  'Band Saw',    1.5
    union all select 2,   20,  'CNC Lathe',   6.00
    union all select 2,   20,  'CNC Lathe',   2.00
    union all select 2,   30,  'CNC Punch',   0.5
    union all select 2,   40,  'Manual Ops',  1.25
    union all select 2,   50,  'Inspection',  0.00
    ), rn as
    (

        select 
            grp= row_number() over (order by WO, op) - row_number() over (partition by wo, wc order by wo, op),
            *
        from t
    )
    select grp, wo, MAX(op) AS MaxOp, wc, sum(time)
    from rn
    group by grp, wo, wc
    ORDER BY wo, MaxOp