萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 編程實戰:多次考試加權平均成績計算與存儲過程實現

業務需求

  • 三張表:web_inputCj_cjb(學生-課程)、web_inputCj_xsksb(課程-考試-權重)、web_inputCj_xscjb(考試-學生-分數)。
  • 根據課程 ID,統計該課程下所有學生的加權總分(各考試分數 × 權重%),寫回 web_inputCj_cjb.cj。

核心查詢:計算加權總分

SELECT 
    xsxx.xh,
    ROUND(SUM(ISNULL(xscjb.cj, 0) * ksxx.cjbl * 0.01), 0) AS final_score
FROM (
    SELECT xh FROM web_inputCj_cjb WHERE kcId = @kcid
) xsxx
LEFT JOIN (
    SELECT xscjb.xh, xscjb.cj, ksxx.cjbl
    FROM web_inputCj_xscjb xscjb
    JOIN (
        SELECT ksid, cjbl FROM web_inputCj_xsksb WHERE kcId = @kcid
    ) ksxx ON xscjb.ksid = ksxx.ksid
) zzcj ON xsxx.xh = zzcj.xh
GROUP BY xsxx.xh;

關鍵點:

  • 以學生主表 xsxx 為駕馭表,LEFT JOIN 確保未參考生也列入(分數為 0)。
  • ISNULL(cj,0) 處理缺考。
  • cjbl * 0.01 將百分比轉小數。
  • ROUND(..., 0) 四捨五入至整數。

存儲過程完整實現(游標逐行更新)

USE [new_wzjw];
GO

ALTER PROCEDURE [dbo].[成績管理_過程化成績錄入_統計最終成績]
    @kcid JXRW_CODE  -- 假設為自訂型別,實際請用 INT/VARCHAR
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @xh VARCHAR(12), @cj FLOAT;

    -- 宣告游標
    DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT 
        ROUND(SUM(ISNULL(xscjb.cj, 0) * ksxx.cjbl * 0.01), 0),
        xsxx.xh
    FROM (
        SELECT xh FROM web_inputCj_cjb WHERE kcId = @kcid
    ) xsxx
    LEFT JOIN (
        SELECT xscjb.xh, xscjb.cj, ksxx.cjbl
        FROM web_inputCj_xscjb xscjb
        JOIN (
            SELECT ksid, cjbl FROM web_inputCj_xsksb WHERE kcId = @kcid
        ) ksxx ON xscjb.ksid = ksxx.ksid
    ) zzcj ON xsxx.xh = zzcj.xh
    GROUP BY xsxx.xh;

    OPEN c1;
    FETCH NEXT FROM c1 INTO @cj, @xh;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE web_inputCj_cjb 
        SET cj = @cj 
        WHERE xh = @xh AND kcId = @kcid;

        FETCH NEXT FROM c1 INTO @cj, @xh;
    END

    CLOSE c1;
    DEALLOCATE c1;
END
GO

效能優化方向(無游標集合式寫法)

-- 單一 UPDATE 完成,效能提升數量級
UPDATE cjb
SET cj = ISNULL(zzcj.final_score, 0)
FROM web_inputCj_cjb cjb
JOIN (
    SELECT 
        xsxx.xh,
        ROUND(SUM(ISNULL(xscjb.cj, 0) * ksxx.cjbl * 0.01), 0) AS final_score
    FROM (
        SELECT xh FROM web_inputCj_cjb WHERE kcId = @kcid
    ) xsxx
    LEFT JOIN (
        SELECT xscjb.xh, xscjb.cj, ksxx.cjbl
        FROM web_inputCj_xscjb xscjb
        JOIN (
            SELECT ksid, cjbl FROM web_inputCj_xsksb WHERE kcId = @kcid
        ) ksxx ON xscjb.ksid = ksxx.ksid
    ) zzcj ON xsxx.xh = zzcj.xh
    GROUP BY xsxx.xh
) zzcj ON cjb.xh = zzcj.xh
WHERE cjb.kcId = @kcid;

優勢:單陳述式、無游標開銷、鎖持有時間短、易於平行化。

擴充考量

  • 權重總和驗證:SUM(cjbl) = 100,否則拋錯。
  • 歷史記錄:更新前將舊分數寫入稽核表。
  • 並發控制:加 UPDLOCK, HOLDLOCK 或啟用快照隔離。
copyright © 萬盛學電腦網 all rights reserved