组和和值，并在每个组中列出连续行。

Question

目前，我在下面的示例数据表中使用group，并对总价值进行求和。

ID  Type   Quantity
1  sampleA   10
2  sampleA   1
3  sampleA   5
4  sampleA   9
5  sampleB   7
6  sampleB   10
7  sampleA   23

  Type    Total(sum)
Sample A     48      
Sample B     17    

现在，我想要的是编写一个查询，它可以在下面的一个单独的列中显示ID范围，这是所需的结果。

  Type    Total(sum)  ID Range
Sample A     48        1-4, 7
Sample B     17        5-6   

PS :实际数据很大，善意地建议最优的解决方案。

Answer 1

假设ID是连续的，那么可以简单地按组获得它的MIN()和MAX()。

-- create the sample table for testing
declare @sample table
(
    ID      int,
    Type        varchar(10),
    Quantity    int
)

-- insert some sample data
insert into @sample 
VALUES
(1,  'sampleA',   10),
(2,  'sampleA',    1),
(3,  'sampleA',    5),
(4,  'sampleA',    9),
(5,  'sampleB',    7),
(6,  'sampleB',   10),
(7,  'sampleA',   23)

-- the query
; with 
cte as
(
    select  *, grp = ID - dense_rank() over(order by Type, ID)
    from    @sample 
),
summary as
(
    select  Type, sum(Quantity) as Total
    from    @sample
    group by Type
)
select  Type, Total, id_range = stuff(id_range, 1, 1, '')
from    summary c
        cross apply
        (
            select  ', ' + convert(varchar(5), min(x.ID)) 
                    + case  when min(x.ID) <> max(x.ID) 
                            then '-' + convert(varchar(5), max(x.ID)) 
                            else '' 
                            end
            from    cte x
            where   x.Type  = c.Type
            group by x.grp  
            for xml path ('')   
        ) r (id_range)

/*  RESULT : 
    Type    Total  id_range
    sampleA    48  1-4, 7
    sampleB    17  5-6
*/

Answer 2

id序列中的空白将使用此语法返回ID范围内的额外数据集项，由于函数塞纳特，您需要sql-server 2012使用此语法。

DECLARE @t table(ID int, Type varchar(10), Quantity int)
INSERT @t values(1  ,'sampleA', 10),(2 ,'sampleA',  1),(3 ,'sampleA', 5),
(4 ,'sampleA', 9),(5 ,'sampleB', 7),(6 ,'sampleB', 10),(7 ,'sampleA', 23)

;WITH CTE as
(
  SELECT
    id, Type, sum(quantity) over(partition by Type) ts,
    id - dense_rank() over (partition by Type order by id) grp
  FROM @t
)
SELECT Type, ts [Total(Sum)],
  STUFF((SELECT ', '+ concat(min(id),'-'+cast(nullif(max(id),min(id)) as varchar(20)))
  FROM CTE x
  WHERE Type  = cte.Type
  GROUP BY x.grp  
  FOR xml path ('')  
  ), 1,2,'') [ID Range]
FROM CTE
GROUP BY Type, ts

结果：

Type    Total(Sum)  ID Range
sampleA         48  1-4, 7
sampleB         17  5-6

Answer 3

您已经指定ID是连续的。但是，我将无视这一假设，并使用LAG和SUM...OVER对组进行编号。

WITH yourdata(ID, Type, Quantity) AS (
    SELECT 1, 'SampleA', 10 UNION
    SELECT 2, 'SampleA', 1 UNION
    SELECT 3, 'SampleA', 5 UNION
    SELECT 4, 'SampleA', 9 UNION
    SELECT 5, 'SampleB', 7 UNION
    SELECT 6, 'SampleB', 10 UNION
    SELECT 7, 'SampleA', 23
), cte_change_flag AS (
    SELECT ID, Type, CASE WHEN LAG(Type) OVER (ORDER BY ID) = Type THEN 0 ELSE 1 END AS chg
    FROM yourdata
), cte_group_number AS (
    SELECT ID, Type, SUM(chg) OVER (ORDER BY ID) AS grp
    FROM cte_change_flag
)
SELECT Type, Quantity, STUFF(XMLCol, 1, 1, '') AS IDRange
FROM (
    SELECT Type, SUM(Quantity) AS Quantity
    FROM yourdata
    GROUP BY Type
) AS main_groups
CROSS APPLY (
    SELECT CONCAT(',', MIN(ID), CASE WHEN MIN(ID) <> MAX(ID) THEN CONCAT('-', MAX(ID)) END)
    FROM cte_group_number
    WHERE Type = main_groups.Type
    GROUP BY grp
    FOR XML PATH('')
) AS sub_groups(XMLCol)

结果：

| Type    | Quantity | IDRange |
|---------|----------|---------|
| SampleA | 48       | 1-4,7   |
| SampleB | 17       | 5-6     |