代码之家 › 专栏 › 技术社区 › Ekaterina Chistyakova

SQL,如何在while循环中正确求和并按周数将其写入表

grouping sum while-loop sql

Ekaterina Chistyakova · 技术社区 · 3 年前

我有一些数据需要正确求和,但我不太清楚如何:

create table #Test
(
Job [nvarchar](20) 
,[Order] [nvarchar](20) 
,WeekNumber int
,startday date
,endday date
,WeeklyHrs decimal(17, 5)
,WhatTheClientWants decimal(17, 5)
,PreviousHrs decimal(17, 5)
)

INSERT INTO #Test
VALUES 
(1,1,1,'2022-05-09','2022-05-15',172,NULL,0),
(1,1,2,'2022-05-16','2022-05-22',9,NULL,0),
(1,2,1,'2022-05-09','2022-05-15',9,NULL,0),
(1,2,999,NULL,NULL,32.5,NULL,32.5),
(1,5,1,'2022-05-09','2022-05-15',162,NULL,0),
(1,5,2,'2022-05-16','2022-05-22',20,NULL,0),
(1,5,3,'2022-05-16','2022-05-22',0,NULL,0),
(1,6,2,'2022-05-16','2022-05-22',1,NULL,0),
(1,3,999,NULL,NULL,32.5,NULL,54)

所以客户端有两个参数:@startdate,@enddate,在这些参数之间我形成了weeks。如2022年5月9日-2022年5月15日-第1周、2022年5月16日、2022年5月22日-第2周。有鉴于此,我后来进行了分组,但现在,新任务出现了。我需要让第二周的时间加上第一周的时间,第三周(可能有很多周)的时间加上第一周、第二周和第三周的时间,依此类推。。。我试着用while更新,但没有安静工作,然后我尝试了窗口功能,但我做错了:

declare @MaxWeek int = (select top 1 max(WeekNumber) over (PARTITION BY job) from #Test where WeekNumber <> 999)
DECLARE @i int = 0

WHILE (@i <= @MaxWeek)
BEGIN
UPDATE #Test
set WhatTheClientWants = (select sum(FullHrs) from #Test group by Job, WeekNumber)
set @i= @i+1
END

我想可能会有这样的情况,如果第一周只有几个小时,但如果不是的话,一定是一笔金额

WHILE (@i <= @MaxWeek)
BEGIN
UPDATE #Test
set WhatTheClientWants = FullHrs from #Test where WeekNumber = @i 
set @i= @i+1
END

但我想不出是什么情况。。。

它应该是什么样子:

非常感谢您的帮助!

0 回复 | 直到 3 年前

JNevill 3 年前

考虑使用窗口函数来执行此操作:

SELECT testtable.*, 
    SUM(WeeklyHours) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable

这是对共享相同“分区”(或[顺序]列值)的每组记录进行排序 startday 然后将它们相加到当前行。分区内的累积和。

虽然总是很容易想到“While LOOP!”作为一种解决方案,在一个几乎从不需要的数据库中。在这个领域的十多年里,我只需要求助于while循环一次,就可以得到一个非常程序化的解决方案,而这个解决方案没有基于集合的替代方案。

刚刚注意到新列中NULL startday的NULL。用工会来打击这一切就足够了:

SELECT testtable.*, 
    SUM(WeeklyHrs) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable
WHERE StartDay IS NOT NULL 
UNION ALL
SELECT testtable.*, NULL
FROM #test as testtable
WHERE StartDay IS NULL

CHill60 3 年前

JNevill解决方案的替代方案

select Job 
,[Order] 
,WeekNumber 
,startday 
,endday 
,WeeklyHrs 
,sum(WeeklyHrs) OVER (PARTITION BY [Order] ORDER BY [Order], WeekNumber )
,PreviousHrs 
from #test

因为您似乎只在 [Order]

顺便提一下,这可能与您的预期结果不匹配-这些结果与您提供的数据根本不匹配-可能是您需要检查的其他内容