比较好的SQL-优快云博客

本文介绍了一种使用T-SQL进行数据批量更新和汇总的方法，包括设置时间范围、删除旧记录、插入新记录并按不同条件进行汇总。该方法适用于需要定期对大量数据进行处理的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DECLARE @Data NVARCHAR(30);
DECLARE @Data2 NVARCHAR(30);
SET @Data = @DataDate;
SET @Data = CONVERT(CHAR(30), DATEADD(HOUR, 8, @Data), 120);
SET @Data2 = CONVERT(CHAR(30), DATEADD(day, 1, @Data), 120);
DELETE FROM dbo.WF_ActionSummary
WHERE ActionDate = CONVERT(CHAR(10), @Data, 120);
INSERT INTO dbo.WF_ActionSummary
( ActionDate ,
ActionType ,
ActionCode ,
CountryCode ,
DeliveryCode ,
ActionComplex ,
JobNumber ,
ActionNumber
)
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
'' 国家 ,
'' 渠道 ,
DifficultyLevel 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'CKI'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY DifficultyLevel ,
a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'GP' 动作分类 ,
'SRT' 动作类别 ,
'' 国家 ,
'' 渠道 ,
1 动作复杂度 ,
'' 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
WHERE ActionTime > @Data
AND a.ActionCode = 'CKI'
AND ActionTime < @Data2
AND a.DeleteUser = 0
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'GP' 动作分类 ,
'CK2' 动作类别 ,
CountryCode 国家 ,
'' 渠道 ,
1 动作复杂度 ,
'' 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'CKI'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
CountryCode 国家 ,
'' 渠道 ,
DifficultyLevel 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'PAW'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
DifficultyLevel ,
a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
CountryCode 国家 ,
'' 渠道 ,
DifficultyLevel 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'MGL'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
DifficultyLevel ,
a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
CountryCode 国家 ,
'' 渠道 ,
DifficultyLevel 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'PIK'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
DifficultyLevel ,
a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
CountryCode 国家 ,
'' 渠道 ,
1 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'CHK'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'GP' 动作分类 ,
'PKG' 动作类别 ,
CountryCode 国家 ,
DeliveryCode 渠道 ,
1 动作复杂度 ,
'' 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'PIK'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
DeliveryCode ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'PS' 动作分类 ,
ActionCode 动作类别 ,
'' 国家 ,
'' 渠道 ,
1 动作复杂度 ,
MAX(b.JobNumber) 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE ActionTime > @Data
AND a.ActionCode = 'PRL'
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY a.ActionUser ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'GP' 动作分类 ,
'ZON' 动作类别 ,
CountryCode 国家 ,
DeliveryCode 渠道 ,
1 动作复杂度 ,
'' 操作员工 ,
COUNT(0) 动作数量
FROM dbo.WF_ActionInstance a WITH ( NOLOCK )
INNER JOIN [Tr.TWX.WebFrame].dbo.Adm_User b WITH ( NOLOCK ) ON a.ActionUser = b.UserID
WHERE a.ActionCode = 'PIK'
AND ActionTime > @Data
AND ActionTime < @Data2
AND a.DeleteUser = 0
AND DeliveryCode <> '0'
GROUP BY CountryCode ,
DeliveryCode ,
ActionCode
UNION ALL
SELECT CONVERT(CHAR(10), @Data, 120) AS 动作日期 ,
'GP' 动作分类 ,
'BAG' 动作类别 ,
CountryCode 国家 ,
DeliveryCode 渠道 ,
1 动作复杂度 ,
'' 操作员工 ,
COUNT(0) 动作数量
FROM WF_ActionInstance a WITH ( NOLOCK )
WHERE ActionCode = 'BAG'
AND ActionTime > @Data
AND ActionTime < @Data2
AND a.DeleteUser = 0
GROUP BY CountryCode ,
DeliveryCode