AI写的不用游标派发明细数量例子

• 需求：

请在sqlserver设计数据库表以及编写sql实现如下需求：有个课目奖金表，登记各课目奖金池； 一个班有36位学生，每人每课目成绩不同，按学号顺序，如果成绩在90-100之间奖励200块，80-89之间奖励100块，70-79之间奖励50,其他不奖励，要考虑奖金是否派完的情况，最好不要用游标实现。首先，学生表要有金额字段，先更新金额字段然后显示这个表内容和各课目奖金池剩余金额

其实这种需求在ERP/MES系统中很多地方都要用，比如CMI登记的是明细，但要需要合并发料，发料后要回写明细。

GPT给出用CTE递归实现的方案，值得学习：
创建测试表及数据：

-- 课目奖金池表
CREATE TABLE SubjectBonus (
    SubjectCode NVARCHAR(50) PRIMARY KEY,  -- 课目代码
    TotalBonus INT                         -- 总奖金池
);

-- 学生表
CREATE TABLE Students (
    StudentID INT,                         -- 学号
    SubjectCode NVARCHAR(50),              -- 课目代码
    Score INT,                             -- 成绩
    RewardAmount INT,                      -- 奖励金额
    PRIMARY KEY (StudentID, SubjectCode)  -- 复合主键
);
-- 插入课目奖金池数据
INSERT INTO SubjectBonus (SubjectCode, TotalBonus)
VALUES ('Math', 10000), ('Science', 10000);  -- 假设每个课目的初始奖金池为 10000

-- 插入学生成绩数据
INSERT INTO Students (StudentID, SubjectCode, Score)
VALUES
(1, 'Math', 95), (2, 'Math', 85), (3, 'Math', 75), -- Math 科目成绩
(1, 'Science', 88), (2, 'Science', 92), (3, 'Science', 70); -- Science 科目成绩

更新学生表中的奖励金额

-- 使用 CTE 计算每个学生的奖金，并插入到临时表
WITH OrderedStudents AS (
    SELECT 
        StudentID,
        SubjectCode,
        Score,
        CASE 
            WHEN Score BETWEEN 90 AND 100 THEN 200
            WHEN Score BETWEEN 80 AND 89 THEN 100
            WHEN Score BETWEEN 70 AND 79 THEN 50
            ELSE 0
        END AS PotentialReward,
        ROW_NUMBER() OVER(PARTITION BY SubjectCode ORDER BY StudentID) AS RowNum
    FROM Students
),
RewardCTE AS (
    -- 初始数据
    SELECT 
        os.StudentID,
        os.SubjectCode,
        os.Score,
        os.PotentialReward,
        sb.TotalBonus AS RemainingBonus,
        os.RowNum
    FROM 
        OrderedStudents os
    JOIN 
        SubjectBonus sb ON os.SubjectCode = sb.SubjectCode
    WHERE 
        os.RowNum = 1
    
    UNION ALL
    
    -- 递归计算奖金
    SELECT 
        os.StudentID,
        os.SubjectCode,
        os.Score,
        CASE 
            WHEN r.RemainingBonus >= os.PotentialReward THEN os.PotentialReward
            ELSE r.RemainingBonus
        END AS PotentialReward,
        r.RemainingBonus - CASE 
                             WHEN r.RemainingBonus >= os.PotentialReward THEN os.PotentialReward
                             ELSE r.RemainingBonus
                           END AS RemainingBonus,
        os.RowNum
    FROM 
        OrderedStudents os
    JOIN 
        RewardCTE r ON os.SubjectCode = r.SubjectCode AND os.RowNum = r.RowNum + 1
)
-- 将 CTE 结果插入临时表
SELECT 
    StudentID,
    SubjectCode,
    PotentialReward,
    RemainingBonus
INTO #TempRewards
FROM RewardCTE
WHERE RowNum = 1 OR EXISTS (
    SELECT 1 
    FROM RewardCTE sub
    WHERE sub.SubjectCode = RewardCTE.SubjectCode 
    AND sub.RowNum = RewardCTE.RowNum - 1
);

-- 更新 Students 表中的 RewardAmount
UPDATE s
SET s.RewardAmount = tr.PotentialReward
FROM Students s
JOIN #TempRewards tr ON s.StudentID = tr.StudentID AND s.SubjectCode = tr.SubjectCode;

-- 更新 SubjectBonus 表中的 TotalBonus
UPDATE sb
SET sb.TotalBonus = sb.TotalBonus - (
    SELECT SUM(s.RewardAmount)
    FROM Students s
    WHERE s.SubjectCode = sb.SubjectCode
)
FROM SubjectBonus sb;

-- 显示更新后的学生表内容
SELECT 
    StudentID, 
    SubjectCode,
    Score, 
    RewardAmount
FROM 
    Students
ORDER BY 
    SubjectCode,
    StudentID;

-- 显示更新后的课目奖金池
SELECT 
    SubjectCode, 
    TotalBonus 
FROM 
    SubjectBonus;

-- 删除临时表
DROP TABLE #TempRewards;