sql - sql-根据最大和分配组

77 0

我需要按行和总和对行进行分组,直到达到阈值为止。

这是我的一些例子数据,阈值为100的尝试:


drop table #table



create table #table (


 Id int not null,


 GroupId int not null,


 Code nvarchar(14) not null,


 Total int not null


)



insert into #table values


( 1, 1, '1111', 20),( 2, 1, '1111', 75),( 3, 1, '1111', 40),( 4, 1, '1111', 20),


( 5, 1, '1111', 20),( 6, 1, '1111', 25),( 7, 1, '2222', 20),( 8, 1, '2222', 20),


( 9, 1, '2222', 20),(10, 1, '2222', 20),(11, 2, '3333', 10),(12, 2, '3333', 90),


(13, 2, '3333', 90),(14, 2, '3333', 90),(15, 2, '3333', 90),(16, 2, '3333', 10),


(17, 2, '3333', 10),(18, 2, '3333', 10),(19, 2, '3333', 10),(20, 2, '3333', 10),


(21, 2, '3333', 10),(22, 2, '3333', 10),(23, 2, '3333', 10),(24, 2, '3333', 10),


(25, 2, '3333', 10),(26, 2, '3333', 10),(27, 2, '3333', 10),(28, 2, '3333', 10),


(29, 2, '3333', 10),(30, 2, '3333', 10),(31, 2, '3333', 10),(32, 2, '3333', 10),


(33, 2, '3333', 10),(34, 2, '3333', 10),(35, 2, '3333', 10)



;with cte as (


 select


 Id, GroupId, Code, Total,


 cast(sum(Total) OVER (ORDER BY Code, id) as int) / 100 AS Limit


 from #table


)


select


 *,


 dense_rank() over(ORDER BY Code, Limit) as Groups


from cte order by id



我手动添加了一个“ GroupsExpected ”列以显示我实际需要的组。


| Id | GroupId | Code | Total | Limit | Groups | GroupsExpected |


|----|---------|------|-------|-------|--------|----------------|


| 1 | 1 | 1111 | 20 | 0 | 1 | 1 |


| 2 | 1 | 1111 | 75 | 0 | 1 | 1 |


| 3 | 1 | 1111 | 40 | 1 | 2 | 2 |


| 4 | 1 | 1111 | 20 | 1 | 2 | 2 |


| 5 | 1 | 1111 | 20 | 1 | 2 | 2 |


| 6 | 1 | 1111 | 25 | 2 | 3 | 3 |


| 7 | 1 | 2222 | 20 | 2 | 4 | 4 |


| 8 | 1 | 2222 | 20 | 2 | 4 | 4 |


| 9 | 1 | 2222 | 20 | 2 | 4 | 4 |


| 10 | 1 | 2222 | 20 | 2 | 4 | 4 |


| 11 | 2 | 3333 | 10 | 2 | 5 | 5 |


| 12 | 2 | 3333 | 90 | 3 | 6 | 6 |


| 13 | 2 | 3333 | 90 | 4 | 7 | 7 |


| 14 | 2 | 3333 | 90 | 5 | 8 | 8 |


| 15 | 2 | 3333 | 90 | 6 | 9 | 9 |


| 16 | 2 | 3333 | 10 | 6 | 9 | 10 |


| 17 | 2 | 3333 | 10 | 6 | 9 | 10 |


| 18 | 2 | 3333 | 10 | 6 | 9 | 10 |


| 19 | 2 | 3333 | 10 | 6 | 9 | 10 |


| 20 | 2 | 3333 | 10 | 7 | 10 | 10 |


| 21 | 2 | 3333 | 10 | 7 | 10 | 10 |


| 22 | 2 | 3333 | 10 | 7 | 10 | 10 |


| 23 | 2 | 3333 | 10 | 7 | 10 | 10 |


| 24 | 2 | 3333 | 10 | 7 | 10 | 10 |


| 25 | 2 | 3333 | 10 | 7 | 10 | 11 |


| 26 | 2 | 3333 | 10 | 7 | 10 | 11 |


| 27 | 2 | 3333 | 10 | 7 | 10 | 11 |


| 28 | 2 | 3333 | 10 | 7 | 10 | 11 |


| 29 | 2 | 3333 | 10 | 7 | 10 | 11 |


| 30 | 2 | 3333 | 10 | 8 | 11 | 11 |


| 31 | 2 | 3333 | 10 | 8 | 11 | 11 |


| 32 | 2 | 3333 | 10 | 8 | 11 | 11 |


| 33 | 2 | 3333 | 10 | 8 | 11 | 11 |


| 34 | 2 | 3333 | 10 | 8 | 11 | 12 |


| 35 | 2 | 3333 | 10 | 8 | 11 | 12 |



每个组的“合计”列的总和不能超过100,并且组“ 9”和“ 10”超出了该数量(它们的总和分别为130和100)。

我可以以编程方式进行操作,但是我觉得这可以通过单一查询更容易地实现。

我正在使用MSSQL 2016。

时间: 原作者:

64 1


declare @table table (


 Id int not null,


 GroupId int not null,


 Code nvarchar(14) not null,


 Total int not null


)



insert into @table values


( 1, 1, '1111', 20),( 2, 1, '1111', 75),( 3, 1, '1111', 40),( 4, 1, '1111', 20),


( 5, 1, '1111', 20),( 6, 1, '1111', 25),( 7, 1, '2222', 20),( 8, 1, '2222', 20),


( 9, 1, '2222', 20),(10, 1, '2222', 20),(11, 2, '3333', 10),(12, 2, '3333', 90),


(13, 2, '3333', 90),(14, 2, '3333', 90),(15, 2, '3333', 90),(16, 2, '3333', 10),


(17, 2, '3333', 10),(18, 2, '3333', 10),(19, 2, '3333', 10),(20, 2, '3333', 10),


(21, 2, '3333', 10),(22, 2, '3333', 10),(23, 2, '3333', 10),(24, 2, '3333', 10),


(25, 2, '3333', 10),(26, 2, '3333', 10),(27, 2, '3333', 10),(28, 2, '3333', 10),


(29, 2, '3333', 10),(30, 2, '3333', 10),(31, 2, '3333', 10),(32, 2, '3333', 10),


(33, 2, '3333', 10),(34, 2, '3333', 10),(35, 2, '3333', 10)



select *


from @table


order by code,id



declare @runtotal int = 0


declare @groups int = 0


declare @code nvarchar(14)


declare @currentcode nvarchar(14) = ''


declare @total int


declare @id int



declare @output table (


 Id int not null,


 Groups int not null


)



declare cursor_table cursor


 for select id, code, total


 from @table


 order by code,id



open cursor_table


fetch next from cursor_table into @id, @code, @total



while @@fetch_status = 0


 begin


 set @runtotal += @total


 if @runtotal >= 100 or @code <> @currentcode


 begin


 set @runtotal = @total


 set @groups += 1


 set @currentcode = @code


 end


 insert into @output


 select @id,@groups


 fetch next from cursor_table into @id, @code, @total


 end



select t.*,groups


from @table t


inner join @output o on o.id=t.id



close cursor_table


deallocate cursor_table



原作者:
...