如何编写MySQL查询筛选累计列值≤指定值的行及按offset limit筛选评审记录

问题1：筛选指定列累计总和≤100的最新行 #

注：你描述的「最后x列」应为笔误，以下按常规业务场景的「最后x行」逻辑实现，若确实需要动态筛选列可基于information_schema.columns拼接动态SQL实现

MySQL 8.0+ 版本（支持窗口函数） #

WITH reverse_cumulative AS (
    SELECT *,
           -- 按主键倒序（即最新行在前）计算指定列的累计和
           SUM(指定列名) OVER(ORDER BY 主键列名 DESC) AS cum_sum
    FROM 表名
)
SELECT * FROM reverse_cumulative
WHERE cum_sum <= 100
ORDER BY 主键列名 DESC;

MySQL 5.x 版本（用变量实现累计） #

SELECT * FROM (
    SELECT t.*,
           @cum := @cum + 指定列名 AS cum_sum
    FROM 表名 t, (SELECT @cum := 0) AS init
    ORDER BY 主键列名 DESC
) AS t_cum
WHERE cum_sum <= 100
ORDER BY 主键列名 DESC;

问题2：评审记录表分页查询实现 #

前提说明 #

表名假设为review_records，传入参数为:offset（起始计数，从1开始）、:limit（取数长度）、:user_id（查询的用户ID）

MySQL 8.0+ 版本实现 #

WITH review_range AS (
    SELECT *,
           -- 计算当前行覆盖的计数区间
           COALESCE(SUM(count) OVER(ORDER BY sno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_range,
           SUM(count) OVER(ORDER BY sno ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS end_range
    FROM review_records
    WHERE userId = :user_id
),
matched_rows AS (
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY sno ASC) AS row_num,
           COUNT(*) OVER() AS total_matched
    FROM review_range
    WHERE start_range <= :offset + :limit - 1
      AND end_range >= :offset
)
SELECT 
    sno, userId, value, count,
    -- 首行跳过的评审数量
    CASE WHEN row_num = 1 THEN :offset - start_range ELSE 0 END AS lower_left_count,
    -- 末行跳过的评审数量
    CASE WHEN row_num = total_matched THEN end_range - (:offset + :limit - 1) ELSE 0 END AS upper_left_count
FROM matched_rows
ORDER BY sno ASC;

MySQL 5.x 版本实现 #

SELECT 
    sno, userId, value, count,
    CASE WHEN row_num = 1 THEN :offset - start_range ELSE 0 END AS lower_left_count,
    CASE WHEN row_num = total_matched THEN end_range - (:offset + :limit - 1) ELSE 0 END AS upper_left_count
FROM (
    SELECT 
        *,
        @row_num := @row_num + 1 AS row_num,
        @total := FOUND_ROWS() AS total_matched
    FROM (
        SELECT 
            t.*,
            @prev_end AS start_range,
            @prev_end := @prev_end + t.count AS end_range
        FROM (
            SELECT * FROM review_records
            WHERE userId = :user_id
            ORDER BY sno ASC
        ) AS t, (SELECT @prev_end := 1, @row_num := 0) AS init
        HAVING start_range <= :offset + :limit - 1 AND end_range >= :offset
        ORDER BY sno ASC
    ) AS matched
) AS res;

lower_left_count和upper_left_count可根据你实际的业务定义调整计算逻辑，核心匹配行的逻辑已覆盖你给出的所有示例场景。

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