sql - 如何在 UPDATE 语句的集合列表中使用聚合

Question

让我描述一下整个情况:我有下面两个表。 Module 表中 MaxTestCasesExecutedBySingleUser 和 TotalTestCasesExecutedByAllUser 的值需要使用一些聚合子句进行更新

CREATE TABLE [dbo].[Module](
    [ProjectID] [int] NOT NULL,
    [ModuleID] [int] NOT NULL,
    [ModuleName] [nvarchar](100) NOT NULL,
    [MaxTestCasesExecutedBySingleUser] [int] NULL,
    [TotalTestCasesExecutedByAllUser] [int] NULL,
    PRIMARY KEY ([ProjectID],[ModuleID]))

CREATE TABLE [dbo].[ModuleMember](
    [ProjectID] [int] NOT NULL,
    [ModuleID] [int] NOT NULL,
    [SerialNo] [int] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    PRIMARY KEY ([ProjectID],[ModuleID],[SerialNo]))

Insert Into Module Values (1,1,'Installation_Test',null,null)
Insert Into Module Values (1,2,'Server_Test',null,null)
Insert Into Module Values (1,3,'Client_Test',null,null)
Insert Into Module Values (1,4,'Security_Test',null,null)

Insert Into ModuleMember Values(1,1,0,'Jim')
Insert Into ModuleMember Values(1,1,1,'Bob')
Insert Into ModuleMember Values(1,2,0,'Jack')
Insert Into ModuleMember Values(1,2,1,'Steve')
Insert Into ModuleMember Values(1,2,2,'Roy')
Insert Into ModuleMember Values(1,2,3,'Jerry')
Insert Into ModuleMember Values(1,3,0,'Root')
Insert Into ModuleMember Values(1,3,1,'Tom')
Insert Into ModuleMember Values(1,4,0,'Evil')

还有另一个表值函数 dbo.GetValue，它以 Name(来自 ModuleMember 表)作为参数并返回 的值>Name 和 TestCasesExecutedByTheUser 以表格的形式。我需要更新 Module 表中的这两个值。

定义:

MaxTestCasesExecutedBySingleUser = 如果测试用例由模块中的成员执行的最大数量

TotalTestCasesExecutedByAllUser = 模块中所有成员执行的测试用例总数。

我尝试了以下查询，但它向我抛出错误:聚合可能不会出现在更新语句的集合列表中。

UPDATE M
SET 
M.MaxTestCasesExecutedBySingleUser = MAX(N.TestCasesExecutedByTheUser),
M.TotalTestCasesExecutedByAllUser = SUM(N.TestCasesExecutedByTheUser)
FROM Module M 
JOIN ModuleMember Mem ON (M.ProjectID = Mem.ProjectID AND M.ModuleID = Mem.ModuleID)
CROSS APPLY dbo.GetValue(Mem.Name) N

Answer 1

解决方法有点冗长 - 您必须先获取聚合(例如在 CTE 中)，然后然后更新目标表:

;WITH src AS
(
  SELECT 
    Mem.ProjectID,  
    Mem.ModuleID,
    mtc = MAX(N.TestCasesExecutedByTheUser),
    stc = SUM(N.TestCasesExecutedByTheUser)
  FROM dbo.ModuleMember AS Mem
  CROSS APPLY dbo.GetValue(Mem.Name) AS N
  GROUP BY Mem.ProjectID, Mem.ModuleID
)
UPDATE M
  SET M.MaxTestCasesExecutedBySingleUser = src.mtc,
      M.TotalTestCasesExecutedBySingleUser = src.stc
FROM dbo.Module AS M
INNER JOIN src 
ON M.ProjectID = src.ProjectID 
AND M.ModuleID = src.ModuleID;