将C#循环逻辑转换为SQL存储过程实现学生数据聚合查询

一、无循环单查询方案（推荐） #

直接利用SQL的集合运算替代循环，效率远高于逐行遍历。根据子查询逻辑的相似度，提供两种实现方式：

方法1：CASE WHEN + 聚合函数（适合逻辑相似的指标） #

如果15个指标的统计逻辑都是基于同一表的不同过滤条件，用CASE WHEN配合聚合函数一次性计算所有指标：

SELECT 
    s.student_id,
    s.rank,
    -- 示例：2个指标，其余13个按相同逻辑扩展
    SUM(CASE WHEN sc.subject = '语文' AND sc.score >= 60 THEN 1 ELSE 0 END) AS chinese_pass_count,
    SUM(CASE WHEN sc.subject = '数学' AND sc.score = 100 THEN 1 ELSE 0 END) AS math_fullmark_count,
    -- 继续添加剩余13个CASE WHEN统计逻辑
FROM 
    (SELECT DISTINCT student_id, rank 
     FROM students 
     WHERE standard = @standard AND last_updated_by = @last_updated_by) s
LEFT JOIN 
    student_scores sc ON s.student_id = sc.student_id
WHERE 
    sc.standard = @standard AND sc.last_updated_by = @last_updated_by
GROUP BY 
    s.student_id, s.rank;

方法2：LEFT JOIN多子查询（适合逻辑差异大的指标） #

如果部分指标的统计逻辑差异较大（比如关联不同表、复杂过滤），可以将每个指标封装为独立子查询，再通过LEFT JOIN关联：

SELECT 
    s.student_id,
    s.rank,
    COALESCE(sc1.chinese_pass_count, 0) AS chinese_pass_count,
    COALESCE(sc2.math_fullmark_count, 0) AS math_fullmark_count,
    -- 继续添加剩余13个子查询的字段
FROM 
    (SELECT DISTINCT student_id, rank 
     FROM students 
     WHERE standard = @standard AND last_updated_by = @last_updated_by) s
LEFT JOIN 
    (SELECT student_id, COUNT(*) AS chinese_pass_count 
     FROM student_scores 
     WHERE subject = '语文' AND score >=60 
       AND standard = @standard AND last_updated_by = @last_updated_by 
     GROUP BY student_id) sc1 ON s.student_id = sc1.student_id
LEFT JOIN 
    (SELECT student_id, COUNT(*) AS math_fullmark_count 
     FROM student_scores 
     WHERE subject = '数学' AND score =100 
       AND standard = @standard AND last_updated_by = @last_updated_by 
     GROUP BY student_id) sc2 ON s.student_id = sc2.student_id
-- 继续添加剩余13个LEFT JOIN子查询;

二、存储过程方案（备选） #

如果业务必须用存储过程，避免使用游标循环，改用临时表+批量更新的方式提升效率：

CREATE PROCEDURE GetStudentScoreSummary
    @standard VARCHAR(50),
    @last_updated_by VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表，包含基础字段和15个指标字段
    CREATE TABLE #StudentSummary (
        student_id INT PRIMARY KEY,
        rank INT,
        chinese_pass_count INT DEFAULT 0,
        math_fullmark_count INT DEFAULT 0,
        -- 添加剩余13个指标字段，默认值设为0
    );

    -- 初始化临时表：插入符合条件的student_id和rank
    INSERT INTO #StudentSummary (student_id, rank)
    SELECT DISTINCT student_id, rank 
    FROM students 
    WHERE standard = @standard AND last_updated_by = @last_updated_by;

    -- 批量更新第一个指标
    UPDATE #StudentSummary
    SET chinese_pass_count = sc.count
    FROM #StudentSummary ss
    JOIN (SELECT student_id, COUNT(*) AS count 
          FROM student_scores 
          WHERE subject='语文' AND score>=60 
            AND standard=@standard AND last_updated_by=@last_updated_by 
          GROUP BY student_id) sc ON ss.student_id = sc.student_id;

    -- 批量更新第二个指标
    UPDATE #StudentSummary
    SET math_fullmark_count = sc.count
    FROM #StudentSummary ss
    JOIN (SELECT student_id, COUNT(*) AS count 
          FROM student_scores 
          WHERE subject='数学' AND score=100 
            AND standard=@standard AND last_updated_by=@last_updated_by 
          GROUP BY student_id) sc ON ss.student_id = sc.student_id;

    -- 继续添加剩余13个批量更新语句

    -- 输出最终汇总结果
    SELECT * FROM #StudentSummary;

    -- 清理临时表
    DROP TABLE #StudentSummary;
END;

关键优化提示 #

1. 所有查询必须带上standard和last_updated_by过滤条件，避免全表扫描；建议给students(standard, last_updated_by)、student_scores(student_id, standard, last_updated_by)创建组合索引。
2. 优先选择无循环的单查询方案，SQL的集合运算效率远高于逐行循环。
3. 如果多个指标有重复的过滤逻辑，可提前用CTE（公共表表达式）过滤数据，减少重复计算。

内容的提问来源于stack exchange，提问作者nsds