Laravel中按Level统计各Score字段Top3分数之和的实现

要实现每个分数字段分别取Top3求和再汇总的逻辑，你可以借助**窗口函数ROW_NUMBER()**对每个team+level分组内的分数排序，筛选出前3名后分别求和，最后将三个字段的求和结果相加。

方法一：Laravel查询构造器实现（原生SQL拼接） #

$data = DB::select(DB::raw("
    SELECT 
        t1.team, 
        t1.level, 
        (t1.sum_score1 + t2.sum_score2 + t3.sum_score3) as total
    FROM (
        SELECT team, level, SUM(score1) as sum_score1
        FROM (
            SELECT 
                team, 
                level, 
                score1,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score1 DESC) as rn
            FROM group_table
        ) sub1
        WHERE rn <=3
        GROUP BY team, level
    ) t1
    JOIN (
        SELECT team, level, SUM(score2) as sum_score2
        FROM (
            SELECT 
                team, 
                level, 
                score2,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score2 DESC) as rn
            FROM group_table
        ) sub2
        WHERE rn <=3
        GROUP BY team, level
    ) t2 ON t1.team = t2.team AND t1.level = t2.level
    JOIN (
        SELECT team, level, SUM(score3) as sum_score3
        FROM (
            SELECT 
                team, 
                level, 
                score3,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score3 DESC) as rn
            FROM group_table
        ) sub3
        WHERE rn <=3
        GROUP BY team, level
    ) t3 ON t1.team = t3.team AND t1.level = t3.level
"));

return response()->json($data);

方法二：CTE优化可读性（适用于MySQL8.0+/PostgreSQL等支持CTE的数据库） #

$data = DB::select(DB::raw("
    WITH score1_top3 AS (
        SELECT team, level, SUM(score1) as sum_score1
        FROM (
            SELECT 
                team, level, score1,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score1 DESC) as rn
            FROM group_table
        ) t WHERE rn <=3
        GROUP BY team, level
    ),
    score2_top3 AS (
        SELECT team, level, SUM(score2) as sum_score2
        FROM (
            SELECT 
                team, level, score2,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score2 DESC) as rn
            FROM group_table
        ) t WHERE rn <=3
        GROUP BY team, level
    ),
    score3_top3 AS (
        SELECT team, level, SUM(score3) as sum_score3
        FROM (
            SELECT 
                team, level, score3,
                ROW_NUMBER() OVER (PARTITION BY team, level ORDER BY score3 DESC) as rn
            FROM group_table
        ) t WHERE rn <=3
        GROUP BY team, level
    )
    SELECT 
        s1.team, 
        s1.level, 
        (s1.sum_score1 + s2.sum_score2 + s3.sum_score3) as total
    FROM score1_top3 s1
    JOIN score2_top3 s2 ON s1.team = s2.team AND s1.level = s2.level
    JOIN score3_top3 s3 ON s1.team = s3.team AND s1.level = s3.level
"));

return response()->json($data);

逻辑说明 #

1. 窗口函数ROW_NUMBER()：按team和level分区，对每个分区内的score1/score2/score3降序排序，给每条记录分配序号rn。
2. 筛选Top3：只保留序号rn <=3的记录，即每个分组内该字段的前3高分。
3. 分别求和：对筛选后的Top3记录按team和level分组求和，得到每个字段的Top3总和。
4. 汇总总分：将三个字段的Top3总和相加，得到最终的total。

注意事项 #

• 确保数据库支持窗口函数（MySQL8.0+、PostgreSQL、SQL Server等均支持）；若使用旧版本MySQL（5.x），可改用关联子查询实现Top3筛选，可补充需求获取适配方案。
• 若某个分组内的某字段记录不足3条，会自动取现有所有记录求和。

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