是否有可能编写一个sql查询，该查询是基于运行的列总数进行分组的?

It would be easier to explain with an example. Suppose I wanted to get at most 5 items per group.

用一个例子来解释比较容易。假设我想要每组最多5个项目。

My input would be a table looking like this:

我的输入是这样的一个表格:

Item    Count
A        2
A        3
A        3
B        4
B        4
B        5
C        1

And my desired output would look like this:

我的期望输出是这样的

Item     Count
A        5
A>5      3
B        4
B>5      9
C        1

An alternative output that I could also work with would be

我还可以使用的另一个输出是

Item    Count    RunningTotal
A       2        2
A       3        5
A       3        8
B       4        4
B       4        8
B       5        13
C       1        1

I can use ROW_NUMBER() to get the top X records in each group, however my requirement is to get the top X items for each group, not X records. My mind is drawing a blank as to how to do this.

我可以使用ROW_NUMBER()获取每个组中的前X条记录，但是我的要求是获得每个组的前X条，而不是X条记录。我的脑子一片空白，不知该怎么做。

6 个解决方案

#1

declare @yourTable table (item char(1), [count] int)

insert into @yourTable
select 'A', 2 union all
select 'A', 3 union all
select 'A', 3 union all
select 'B', 4 union all
select 'B', 4 union all
select 'B', 5 union all
select 'C', 1

;with cte(item, count, row) as (
    select *, row_number() over ( partition by item order by item, [count]) 
    from @yourTable
)
select t1.Item, t1.Count, sum(t2.count) as RunningTotal from cte t1
join cte t2 on t1.item = t2.item and t2.row <= t1.row
group by t1.item, t1.count, t1.row

Result:

结果:

Item Count       RunningTotal
---- ----------- ------------
A    2           2
A    3           5
A    3           8
B    4           4
B    4           8
B    5           13
C    1           1

#2

Considering the clarifications from your comment, you should be able to produce the second kid of output from your post by running this query:

考虑到您的评论的澄清，您应该能够通过运行这个查询从您的帖子中生成第二个输出子儿:

select t.Item
,   t.Count
,   (select sum(tt.count)
    from mytable tt
    where t.item=tt.item and (tt.creating_user_priority < t.creating_user_priority or
        ( tt.creating_user_priority = t.creating_user_priority and tt.created_date < t.createdDate))
    ) as RunningTotal
from mytable t

#3

declare @yourTable table (item char(1), [count] int)

insert into @yourTable
select 'A', 2 union all
select 'A', 3 union all
select 'A', 3 union all
select 'B', 4 union all
select 'B', 4 union all
select 'B', 5 union all
select 'C', 1

;with cte(item, count, row) as (
    select *, row_number() over ( partition by item order by item, [count]) 
    from @yourTable
)

select t1.row, t1.Item, t1.Count, sum(t2.count) as RunningTotal
into #RunTotal
from cte t1
join cte t2 on t1.item = t2.item and t2.row <= t1.row
group by t1.item, t1.count, t1.row


alter table #RunTotal
add GrandTotal int

update rt
set GrandTotal = gt.Total
from #RunTotal rt
left join ( 
    select Item, sum(Count) Total
    from #RunTotal rt
    group by Item) gt
on rt.Item = gt.Item


select Item, max(RunningTotal)
from #RunTotal
where RunningTotal <= 5
group by Item

union

select a.Item + '>5', total - five
from (
    select Item, max(GrandTotal) total
    from #RunTotal
    where GrandTotal > 5
    group by Item
) a
left join (
    select Item, max(RunningTotal) five
    from #RunTotal
    where RunningTotal <= 5
    group by Item
) b
    on a.Item = b.Item

I've updated the accepted answer and got your desired result.

我更新了已接受的答案并得到了你想要的结果。

#4

SELECT Item, SUM(Count)
FROM mytable t
GROUP BY Item
HAVING SUM(Count) <=5 

UNION  

SELECT Item,  5
FROM mytable t
GROUP BY Item
HAVING SUM(Count) >5 

UNION  


SELECT t2.Item + '>5', Sum(t2.Count) - 5
FROM mytable t2
GOUP BY Item
HAVING SUM(Count) > 5

ORDER BY 1, 2

#5

select 'A' as Name, 2 as Cnt
into #tmp
union all select 'A',3
union all select 'A',3
union all select 'B',4
union all select 'B',4
union all select 'B',5
union all select 'C',1

select Name, case when sum(cnt) > 5 then 5 else sum(cnt) end Cnt
from #tmp
group by Name
union 
select Name+'>5', sum(cnt)-5 Cnt
from #tmp
group by Name
having sum(cnt) > 5

Here is what I have so far. I know it's not complete but... this should be a good starting point.

这是我目前所拥有的。我知道这还不完整，但是……这应该是一个好的起点。

#6

I can get your second output by using a temp table and an update pass:

我可以通过使用临时表和update pass来获得您的第二个输出:

DECLARE @Data TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY
    ,Value VARCHAR(5)
    ,Number INT
    ,Total INT
)
INSERT INTO @Data (Value, Number) VALUES ('A',2)
INSERT INTO @Data (Value, Number) VALUES ('A',3)
INSERT INTO @Data (Value, Number) VALUES ('A',3)
INSERT INTO @Data (Value, Number) VALUES ('B',4)
INSERT INTO @Data (Value, Number) VALUES ('B',4)
INSERT INTO @Data (Value, Number) VALUES ('B',5)
INSERT INTO @Data (Value, Number) VALUES ('C',1)

DECLARE 
    @Value VARCHAR(5)
    ,@Count INT

UPDATE @Data
SET
    @Count = Total = CASE WHEN Value = @Value THEN Number + @Count ELSE Number END
    ,@Value = Value
FROM @Data AS D

SELECT 
    Value
    ,Number
    ,Total 
FROM @Data

There may be better ways, but this should work.

也许有更好的方法，但这应该是可行的。

#1

declare @yourTable table (item char(1), [count] int)

insert into @yourTable
select 'A', 2 union all
select 'A', 3 union all
select 'A', 3 union all
select 'B', 4 union all
select 'B', 4 union all
select 'B', 5 union all
select 'C', 1

;with cte(item, count, row) as (
    select *, row_number() over ( partition by item order by item, [count]) 
    from @yourTable
)
select t1.Item, t1.Count, sum(t2.count) as RunningTotal from cte t1
join cte t2 on t1.item = t2.item and t2.row <= t1.row
group by t1.item, t1.count, t1.row