时间戳

首页 > 解决方案 > 如果在使用聚合函数时存在重复，如何仅更新 1 行

sql - 如果在使用聚合函数时存在重复，如何仅更新 1 行

问题描述

我正在尝试创建一个存储过程，它基于SUM(TotalTime). 我的困境在于重复的记录。它增加了两次午餐。请参阅下面的屏幕截图。我希望根据工作的总时间，每位员工每天只添加一次午餐。这是我到目前为止所拥有的。

CREATE TABLE TimeCards 
(
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    EmployeeID nvarchar(50),
    Date DateTime,
    Lunch decimal(10,2),
    TotalTime decimal(10,2)
)

INSERT INTO TimeCards (EmployeeID, Date, Lunch, TotalTime)
VALUES ('1001', '2021-02-04 00:00:00.000', Null, 8) 
, ('136', '2021-02-04 00:00:00.000', Null, 4)
, ('136', '2021-02-04 00:00:00.000', Null, 4)
, ('418', '2021-02-04 00:00:00.000', Null, 5)
, ('418', '2021-02-04 00:00:00.000', Null, 5)
, ('511', '2021-02-04 00:00:00.000', Null, 5)
, ('511', '2021-02-04 00:00:00.000', Null, 6)

UPDATE TimeCards 
SET Lunch = CASE 
               WHEN SUMTotalTime BETWEEN 5.501 AND 11 THEN .5
               WHEN SUMTotalTime BETWEEN 11.01 AND 16 THEN 1
               WHEN SUMTotalTime >= 16.01 THEN 1.5
            END 
FROM 
    (SELECT 
         tc.EmployeeID, Date, MAX(totaltime) AS totaltime,  
         SUM(TotalTime) AS SUMTotalTime 
     FROM 
         TimeCards tc
     GROUP BY 
         tc.EmployeeID, Date 
     HAVING 
         SUM(Totaltime) > 5.5) grouped 
 WHERE 
     TimeCards.totaltime = grouped.totaltime 
     AND TimeCards.Date = grouped.Date 
     AND TimeCards.EmployeeID = grouped.EmployeeID 
     AND grouped.SUMTotalTime > 5.5 
     AND Lunch IS NULL

查询的当前结果和预期结果

标签： sqlsql-serversql-server-2014

解决方案

似乎您想要的是可更新的 CTE：

WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND Lunch IS NULL
  AND RN = 1;

db<>小提琴

如果有人可能已经添加了午餐，并且它可能不是“最后”行，您可以检查“最大午餐”是否是NULL：

WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           MAX(Lunch) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS MaxLunch,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND MaxLunch IS NULL
  AND RN = 1;

db<>小提琴

推荐阅读