替换 SQL Server 2005 游标

Question

这是简化的光标：

SET IMPLICIT_TRANSACTIONS ON

SET @curTemp = CURSOR FAST_FORWARD
FOR
    SELECT gpno, ssn FROM EligCov Group BY gpno, ssn
OPEN @curTemp

-- loop through cursor and build output report table
FETCH NEXT FROM @curTemp INTO @gpno, @ssn
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @trnCnt = @trnCnt + 1

    -- get the max CovEndDate for this group/ssn combo
    SELECT @MaxCovEndDate=MAX(CovEndDate) FROM Payroll WHERE GroupNo=@gpno AND SSN=@ssn
    UPDATE EligCov SET CovEndDate = @MaxCovEndDate WHERE gpno=@gpno AND ssn=@ssn

    -- check transaction counts and commit if needed
    IF @trnCnt % 2000 = 0
    BEGIN
        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT
        END
    END
    FETCH NEXT FROM @curTemp INTO @gpno, @ssn
END
CLOSE @curTemp
DEALLOCATE @curTemp 

SET IMPLICIT_TRANSACTIONS OFF

基本上表 EligCov 有一个不同的 groupNo/SSN 组合。表 Payroll 将有许多 groupno/ssn 组合。 Payroll 表中的每一行都有一个包含日期的 CovEndDate 列。

我只需从 Payroll 中为特定 gpno/ssn 组合（可以是一行或数百行）选择 max(CovEndDate)并更新 EligCov 表中的 CovEndDate（特定 groupno/ssn 组合始终为一行）。

payroll 表有 10,000,000 多行，EligCov 表有约 200,000 行。有没有办法改变它以不使用光标？

重申一下，对于 EligCov 中的每个 group/ssn，请查看 Payroll 中具有相同 group/ssn 的所有记录> 并获取 max(CovEndDate) 并更新 EligCov 表中的 CovEndDate。

谢谢。

Answer 1

当然 - 非常简单 - 使用 CTE（通用表表达式）按“分组”集对数据进行分区。

;WITH DataToUpdate AS
(
    SELECT  
        GroupNo, SSN, CovEndDate AS 'MaxCovEndDate',
        ROW_NUMBER() OVER(PARTITION BY GroupNo,SSN ORDER BY CovEndDate DESC) AS 'RowNum'
    FROM dbo.Payroll 
UPDATE 
)
UPDATE dbo.EligCov 
SET CovEndDate = d.MaxCovEndDate 
FROM DataToUpdate d
WHERE gpno = d.GroupNo 
  AND ssn = d.SSN 
  AND d.RowNum = 1   -- select the latest date - the one with RowNum = 1

CTE 按 GroupNo,SSN 对数据进行分区 - 因此每组 GroupNo,SSN 都会分配新的 ROW_NUMBER()，从 1 开始。由于行按 CovEndDate DESC 排序，因此最新/最近的 CovEndDate 获取 RowNum = 1。

然后，您可以根据该 CTE 更新 EligCov 表，仅获取每组 GroupNo,SSN 的最新条目

Answer 2

一种可能的方法：

UPDATE
    EC
SET
    CovEndDate = SQ.CovEndDate
FROM
    EligCov EC
INNER JOIN (
        SELECT gpno, ssn, MAX(CovEndDate) AS max_dt
        FROM Payroll
        GROUP BY gpno, ssn) SQ ON
    SQ.gpno = EC.gpno AND
    SQ.ssn = EC.ssn

更重要的是，我想问是否真的有必要在 EligCov 中包含该列，这违反了关系数据库的规范化规则。