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

本文主要是介绍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 200WHEN Score BETWEEN 80 AND 89 THEN 100WHEN Score BETWEEN 70 AND 79 THEN 50ELSE 0END AS PotentialReward,ROW_NUMBER() OVER(PARTITION BY SubjectCode ORDER BY StudentID) AS RowNumFROM Students
),
RewardCTE AS (-- 初始数据SELECT os.StudentID,os.SubjectCode,os.Score,os.PotentialReward,sb.TotalBonus AS RemainingBonus,os.RowNumFROM OrderedStudents osJOIN SubjectBonus sb ON os.SubjectCode = sb.SubjectCodeWHERE os.RowNum = 1UNION ALL-- 递归计算奖金SELECT os.StudentID,os.SubjectCode,os.Score,CASE WHEN r.RemainingBonus >= os.PotentialReward THEN os.PotentialRewardELSE r.RemainingBonusEND AS PotentialReward,r.RemainingBonus - CASE WHEN r.RemainingBonus >= os.PotentialReward THEN os.PotentialRewardELSE r.RemainingBonusEND AS RemainingBonus,os.RowNumFROM OrderedStudents osJOIN 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 subWHERE 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 sWHERE 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;