SQL每小时累积数量总和

debugcn 发表于 Dev

穆斯塔法

下表存储指定工序（操作数）中的加工产品数量（加工数量）。我想从该表中获取的是在任何必要的时间具有每小时累积的总数量。

我尝试了两种不同的方法来实现我想要的。

第一种循环方法

Declare @hour tinyint;
SET @hour=8;
Declare @date datetime;
SET @date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
while (@hour<=DATEPART(HOUR, GETDATE()))
Begin
       select lineid, sectionid, operid, sum(processedquantity) as 'CumulativeSum', DATEADD(hour,@hour,@date) as 'UntilTime' from PROCESSBUNDLE
             where PROCESSBUNDLE.PROCESSEND>=@date
       and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
       and SECTIONID=40 
       and PROCESSEND<=DATEADD(hour,@hour,@date)
       group by LINEID, SECTIONID, OPERID
       order by OPERID asc;
       SET @hour=@hour+1;
end

上面的查询循环可以计算我想要的内容，但不幸的是，所有结果都在不同的查询中。因此结果将如下所示：

Group By的第二种方法

Declare @hour tinyint;
SET @hour=DATEPART(HOUR, GETDATE())+1;
Declare @date datetime;
SET @date = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

select lineid, sectionid, operid, sum(processedquantity) as 'Adet', DATEPART(HOUR, PROCESSEND)+1 as 'UntilHour' from PROCESSBUNDLE
       where PROCESSEND>=@date
       and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
       and SECTIONID=40 
       and PROCESSEND<=DATEADD(hour,@hour,@date)
       group by LINEID, SECTIONID, OPERID, DATEPART(HOUR, PROCESSEND)
       order by OPERID, 'UntilHour' asc;

该查询不能给我累积的结果，但是由于每小时的“分组依据”，我可以得到我想要的外观。这些是结果：

那么有可能在一个结果集中获得累积结果吗？这是我要实现的目标：

蚂蚁DC

这就是你想要的......

CREATE TABLE #PROCESSBUNDLE (
    BUNDLEID varchar(50) not null, 
    LINEID varchar(50) not null,
    SECTIONID varchar(50) not null,
    OPERID varchar(50) not null,
    PROCESSBEGIN datetime not null,
    PROCESSEND datetime not null,
    PROCESSEDQUANTITY int not null );

GO

INSERT INTO #PROCESSBUNDLE
    ([BUNDLEID], [LINEID], [SECTIONID], [OPERID], [PROCESSBEGIN], [PROCESSEND], [PROCESSEDQUANTITY])
VALUES
    ('2016031460', '01', '40', '0080004', '2016-Oct-18 14:40:41.000', '2016-Oct-18 14:46:17.193', 20),
    ('2016031360', '01', '40', '3020001', '2016-Oct-18 08:02:04.603', '2016-Oct-18 08:08:47.420', 15),
    ('2016031368', '01', '40', '3020001', '2016-Oct-18 09:10:39.860', '2016-Oct-18 09:15:38.170', 12),
    ('2016031432', '01', '40', '3020001', '2016-Oct-18 09:50:54.743', '2016-Oct-18 10:05:11.560', 13),
    ('2016031437', '01', '40', '3020001', '2016-Oct-18 11:09:55.570', '2016-Oct-18 11:15:27.733', 20),
    ('2016031450', '01', '40', '3020001', '2016-Oct-18 12:00:59.473', '2016-Oct-18 12:10:30.467', 15),
    ('2016031540', '01', '40', '3020001', '2016-Oct-18 14:35:13.067', '2016-Oct-18 14:42:45.613', 14),
    ('2016031323', '01', '40', '3120010', '2016-Oct-18 08:18:05.723', '2016-Oct-18 08:22:13.333', 20),
    ('2016031333', '01', '40', '3120010', '2016-Oct-18 08:56:33.393', '2016-Oct-18 09:03:56.020', 20),
    ('2016031341', '01', '40', '3120010', '2016-Oct-18 09:35:36.240', '2016-Oct-18 09:40:17.470', 17),
    ('2016031346', '01', '40', '3120010', '2016-Oct-18 10:37:38.190', '2016-Oct-18 10:43:16.990', 17),
    ('2016031356', '01', '40', '3120010', '2016-Oct-18 11:29:47.540', '2016-Oct-18 11:34:47.130', 20),
    ('2016031368', '01', '40', '3120010', '2016-Oct-18 12:13:15.887', '2016-Oct-18 12:16:10.800', 12),
    ('2016031434', '01', '40', '3120010', '2016-Oct-18 13:24:22.120', '2016-Oct-18 13:27:46.367', 20),
    ('2016031444', '01', '40', '3120010', '2016-Oct-18 14:05:44.710', '2016-Oct-18 14:12:36.430', 20),
    ('2016029572', '01', '40', '3190000', '2016-Oct-18 07:54:58.873', '2016-Oct-18 08:01:37.667', 17),
    ('2016031285', '01', '40', '3140000', '2016-Oct-18 07:59:18.137', '2016-Oct-18 08:21:45.207', 17),
    ('2016031287', '01', '40', '3190000', '2016-Oct-18 09:56:59.367', '2016-Oct-18 10:08:59.743', 17),
    ('2016031315', '01', '40', '3190000', '2016-Oct-18 08:34:50.027', '2016-Oct-18 08:42:57.040', 13),
    ('2016031324', '01', '40', '3190000', '2016-Oct-18 09:07:19.597', '2016-Oct-18 09:14:57.113', 20),
    ('2016031330', '01', '40', '3140000', '2016-Oct-18 07:30:15.730', '2016-Oct-18 07:41:43.390', 15),
    ('2016031338', '01', '40', '3190000', '2016-Oct-18 11:08:30.757', '2016-Oct-18 11:15:43.453', 17),
    ('2016031342', '01', '40', '3140000', '2016-Oct-18 09:02:01.737', '2016-Oct-18 09:26:24.780', 16),
    ('2016031346', '01', '40', '3190000', '2016-Oct-18 11:52:23.667', '2016-Oct-18 11:58:22.227', 17),
    ('2016031350', '01', '40', '3140000', '2016-Oct-18 09:57:29.077', '2016-Oct-18 10:39:06.273', 20),
    ('2016031356', '01', '40', '3190000', '2016-Oct-18 13:26:02.440', '2016-Oct-18 13:30:53.807', 20),
    ('2016031360', '01', '40', '3140000', '2016-Oct-18 11:08:58.843', '2016-Oct-18 11:30:53.213', 15),
    ('2016031365', '01', '40', '3140000', '2016-Oct-18 11:30:53.213', '2016-Oct-18 12:00:02.970', 20),
    ('2016031438', '01', '40', '3140000', '2016-Oct-18 12:08:46.970', '2016-Oct-18 12:35:02.767', 20),
    ('2016031444', '01', '40', '3140000', '2016-Oct-18 13:36:11.650', '2016-Oct-18 14:04:19.220', 20),
    ('2016031559', '01', '40', '3140000', '2016-Oct-18 14:48:08.700', '2016-Oct-18 14:53:47.587', 20),
    ('2016029572', '01', '40', '3170010', '2016-Oct-18 07:29:35.693', '2016-Oct-18 07:49:48.240', 17),
    ('2016029582', '01', '40', '3240000', '2016-Oct-18 07:53:46.757', '2016-Oct-18 07:54:46.723', 14),
    ('2016031164', '01', '40', '3260000', '2016-Oct-18 07:46:28.670', '2016-Oct-18 07:54:32.370', 20),
    ('2016031167', '01', '40', '3250002', '2016-Oct-18 08:00:18.847', '2016-Oct-18 08:08:33.143', 13),
    ('2016031172', '01', '40', '3260000', '2016-Oct-18 09:13:13.433', '2016-Oct-18 09:17:35.810', 13),
    ('2016031173', '01', '40', '3260000', '2016-Oct-18 08:45:57.543', '2016-Oct-18 08:46:04.777', 17),
    ('2016031287', '01', '40', '3240000', '2016-Oct-18 12:06:09.583', '2016-Oct-18 12:12:50.987', 17)


SELECT 
  lineid, sectionid, operid, UntilHour, Adet, SUM(Adet) 
  OVER(PARTITION BY lineid, sectionid, operid 
  ORDER BY lineid, sectionid, operid, UntilHour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal
FROM 
(
  SELECT SUB_Q.lineid, SUB_Q.sectionid, SUB_Q.operid, SUB_Q.UntilHour, SUM(SUB_Q.processedquantity) as Adet
  FROM
  (
    select lineid, sectionid, operid, DATEPART(HOUR, PROCESSEND)+1 as UntilHour, processedquantity
    from #PROCESSBUNDLE
    where 1=1
    --and OPERID in (SELECT OPERID FROM [VTRR].[dbo].[MODELOPER] where MONITOR='1')
    and SECTIONID=40 
    --and PROCESSEND<=DATEADD(hour,@hour,@date)
  ) SUB_Q
  GROUP BY SUB_Q.lineid, SUB_Q.sectionid, SUB_Q.operid, SUB_Q.UntilHour
)TOTALS_Q
ORDER BY lineid, sectionid, operid, UntilHour

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。