SQL Server-计数行，并在列中重新启动某些值

Question

我的数据集看起来大致如下：我的数据集看起来大致如下：

Docnum      linenum DocDate     CardName            Quantity    ItemCode        ItemName                                        DistNumber  MnfSerial       StockPrice  BaseRef U_mwaiOrderType
11807       0       2016-05-31  Central Catholic    1.000000    ConfiguredItem  ConfiguredItem                                  NULL        NULL            0.000000    7900    06
11807       1       2016-05-31  Central Catholic    1.000000    A5YN017         Bizhub 654e B&W Mfp with DF                     I0722       A5YN017008479   4937.400000 7900    06
11807       2       2016-05-31  Central Catholic    1.000000    A3EPWYE         FS-534SD Staple Finisher w/ Saddle Stitch       NULL        A3EPWYA000404   1081.090000 7900    06
11807       3       2016-05-31  Central Catholic    1.000000    A87JWY1         RU-513 Relay Unit (FS-534 bundle component)                 A87JWY1087196   56.900000   7900    06
11807       4       2016-05-31  Central Catholic    1.000000    A3ETW11         Pk-520 2/3 Hole Punch Kit                                   A3ETW11133335   200.000000  7900    06
11807       5       2016-05-31  Central Catholic    1.000000    A0W4WY3         WT-506 Working Table                            NULL        NULL            49.730000   7900    06
11807       6       2016-05-31  Central Catholic    1.000000    7640017928      Uk-204 2gb Mem Upgrade                          NULL        NULL            63.540000   7900    06
11807       7       2016-05-31  Central Catholic    1.000000    A0PD11H         Lk-101 V.3 I-Option Lic (electronic version)    NULL        NULL            31.380000   7900    06
11807       8       2016-05-31  Central Catholic    1.000000    D5143NTKM       Network 120/20 Qc Digital                       NULL        NULL            109.320000  7900    06
11807       10      2016-05-31  Central Catholic    1.000000    ConfiguredItem  ConfiguredItem                                  NULL        NULL            0.000000    7900    06
11807       11      2016-05-31  Central Catholic    1.000000    A5YN017         Bizhub 654e B&W Mfp with DF                     I0725       A5YN017008483   4937.400000 7900    06
11807       12      2016-05-31  Central Catholic    1.000000    A3EPWYE         FS-534SD Staple Finisher w/ Saddle Stitch                   A3EPWYE002891   1069.710000 7900    06
11807       13      2016-05-31  Central Catholic    1.000000    A87JWY1         RU-513 Relay Unit (FS-534 bundle component)                 A87JWY1087022   68.280000   7900    06
11807       14      2016-05-31  Central Catholic    1.000000    A3ETW11         Pk-520 2/3 Hole Punch Kit                                   A3ETW11147373   200.000000  7900    06
11807       15      2016-05-31  Central Catholic    1.000000    A0W4WY3         WT-506 Working Table                            NULL        NULL            49.730000   7900    06
11807       16      2016-05-31  Central Catholic    1.000000    7640017928      Uk-204 2gb Mem Upgrade                          NULL        NULL            63.540000   7900    06
11807       17      2016-05-31  Central Catholic    1.000000    A0PD11H         Lk-101 V.3 I-Option Lic (electronic version)    NULL        NULL            31.380000   7900    06
11807       18      2016-05-31  Central Catholic    1.000000    D5143NTKM       Network 120/20 Qc Digital                       NULL        NULL            109.320000  7900    06
11807       20      2016-05-31  Central Catholic    1.000000    ConfiguredItem  ConfiguredItem                                  NULL        NULL            0.000000    7900    06
11807       21      2016-05-31  Central Catholic    1.000000    A61F011         Bizhub 364e B&W Mfp                             I0726       A61F011024142   1706.220000 7900    06
11807       22      2016-05-31  Central Catholic    1.000000    A3CEWY1         Df-701 Single Pass Df                                       A3CEWY1335693   718.410000  7900    06
11807       23      2016-05-31  Central Catholic    1.000000    A2XM019         Pc-410 Lrg Cap Cassette                                     A2XM019028485   531.790000  7900    06
11807       24      2016-05-31  Central Catholic    1.000000    A4MF012         Fk-511 Fax Kit                                              A4MF012169575   390.660000  7900    06
11807       25      2016-05-31  Central Catholic    1.000000    A3EPWY3         FS-534 Staple Finisher 227/Pro C754e            NULL        A3EPWY2040480   511.020000  7900    06
11807       26      2016-05-31  Central Catholic    1.000000    A87JWY1         RU-513 Relay Unit (FS-534 bundle component)                 A87JWY1087018   63.160000   7900    06
11807       27      2016-05-31  Central Catholic    1.000000    A0W4WY3         WT-506 Working Table                            NULL        NULL            49.730000   7900    06
11807       28      2016-05-31  Central Catholic    1.000000    A0PD11H         Lk-101 V.3 I-Option Lic (electronic version)    NULL        NULL            31.380000   7900    06
11807       29      2016-05-31  Central Catholic    1.000000    7640017928      Uk-204 2gb Mem Upgrade                          NULL        NULL            63.540000   7900    06
11807       30      2016-05-31  Central Catholic    1.000000    D5133NTKM       Digital 120/15 Network Qc                       NULL        NULL            99.110000   7900    06
11807       32      2016-05-31  Central Catholic    1.000000    ConfiguredItem  ConfiguredItem                                  NULL        NULL            0.000000    7900    06
11807       33      2016-05-31  Central Catholic    1.000000    A6F7011         Bizhub 4750 B&W Mfp                             I0727       A6F7011006859   1199.000000 7900    06
11807       34      2016-05-31  Central Catholic    1.000000    A6EDW11         Fk-512 Fax Kit                                              A6EDW11029370   132.000000  7900    06
11807       35      2016-05-31  Central Catholic    1.000000    A6VGWY1         Mk-P03 Fax Mount Kit                            NULL        NULL            37.760000   7900    06
11807       36      2016-05-31  Central Catholic    1.000000    MFP1            120/15 Network Surge                            NULL        NULL            51.910000   7900    06

（有更多的数据 - 只是一个示例）

基本上，我希望能够将项目代码列中两个" configuretItem"之间所有内容的成本进行亚关系。（例如，我想要第0-8、10-18号线等股票的小计）

）

我找不到任何要分组的东西，所以我想知道SQL中是否有某种方法可以添加一列，该列将为Liennums 0-8，2 for 10-18等。

我熟悉ROW_NUMBER()功能，但是在这种情况下，我想不出一种方法适合我的计划。

我知道我需要做一个UNION才能加入小计的表。我也很乐意使用UNION进行ORDER BY，以按照我想要的顺序获取它们。我只是错过了弄清楚如何分组的部分。

Answer 1

尝试一下，我认为它可能对您有用。

Drop Table #Test
Create Table #Test (Docnum Int, linenum Int, ItemCode Varchar(8000), StockPrice Decimal(15,6))
Insert #Test Values (11807, 0, 'ConfiguredItem',  0.000000)
Insert #Test Values (11807, 1, 'A5YN017',    4937.400000) 
Insert #Test Values (11807, 2, 'A3EPWYE',    1081.090000) 
Insert #Test Values (11807, 3, 'A87JWY1',    56.900000)   
Insert #Test Values (11807, 4, 'A3ETW11',    200.000000)  
Insert #Test Values (11807, 5, 'A0W4WY3',    49.730000)   
Insert #Test Values (11807, 6, '7640017928', 63.540000)   
Insert #Test Values (11807, 7, 'A0PD11H',    31.380000)   
Insert #Test Values (11807, 8, 'D5143NTKM',  109.320000)  
Insert #Test Values (11807, 10, 'ConfiguredItem',  0.000000)    
Insert #Test Values (11807, 11, 'A5YN017',    4937.400000) 
Insert #Test Values (11807, 12, 'A3EPWYE',    1069.710000) 
Insert #Test Values (11807, 13, 'A87JWY1',    68.280000)   
Insert #Test Values (11807, 14, 'A3ETW11',    200.000000)  
Insert #Test Values (11807, 15, 'A0W4WY3',    49.730000)   
Insert #Test Values (11807, 16, '7640017928', 63.540000)   
Insert #Test Values (11807, 17, 'A0PD11H',    31.380000)   
Insert #Test Values (11807, 18, 'D5143NTKM',  109.320000)  
Insert #Test Values (11807, 20, 'ConfiguredItem',  0.000000)
;With cteGrouped As
(
Select  *,
        Sum(GroupMark) Over (Order By LineNum) GroupNum
    From
        (Select *,
                Case When ItemCode = 'ConfiguredItem' Then 1 Else 0 End GroupMark       
            From #Test
        ) A
)
Select  *,
        Sum(StockPrice) Over (Partition By GroupNum Order By LineNum) RunningTotal
    From cteGrouped

Answer 2

如果您可以访问公共表格（CTE），则它们可能非常强大。注意：这可能需要优化，具体取决于您必须使用/处理多少行。

DECLARE @tblData TABLE
    (
        DocNum varchar(100)
        , linenum int
        , ItemCode varchar(100)
        , StockPrice decimal(18,6)
    )
INSERT INTO @tblData    
    SELECT '11807',0, 'ConfiguredItem', 0
    UNION SELECT '11807',1, 'A5YN017', 4937.400000
    UNION SELECT '11807',2, 'A3EPWYE', 1081.090000
    UNION SELECT '11807',10, 'ConfiguredItem', 0
    UNION SELECT '11807',11, 'A5YN017', 4937.400000
    UNION SELECT '11807',12, 'A3EPWYE', 1069.710000
    UNION SELECT '11807',20, 'ConfiguredItem', 0
    UNION SELECT '11807',21, 'A61F011', 1706.220000
    UNION SELECT '11807',22, 'A3CEWY1', 718.410000
;WITH CTE_ROWNUM(linenum, rownum)
AS
(
    SELECT 
        linenum
        , ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY linenum)
        FROM @tblData
            WHERE ItemCode = 'ConfiguredItem'
        GROUP BY ItemCode, linenum
)
, CTE_GROUP_BY(linenum, linenumStart, linenumEnd)
AS
(
    SELECT D.linenum,
        D.linenum,
        (CASE WHEN (SELECT linenum FROM CTE_ROWNUM R WHERE R.rownum = C.rownum + 1) IS NULL 
            THEN (SELECT MAX(linenum) FROM @tblData)  --get the last set
            ELSE 
                (SELECT linenum FROM CTE_ROWNUM R WHERE R.rownum = C.rownum + 1)
            END)
        FROM CTE_ROWNUM C
            INNER JOIN @tblData D
                ON C.linenum = D.linenum
)
SELECT GB.linenumStart, SUM(StockPrice) As Total
    FROM @tblData D
    INNER JOIN CTE_GROUP_BY GB
        ON D.linenum BETWEEN GB.linenumStart AND GB.linenumEnd
    GROUP BY GB.linenumStart