MySQL 5.5环境下无OVER函数实现按站点排序并带Limit的逐行累加求和方案

First, let's recap your problem with the sample data and desired output:

Input Table #

Desired Output #

Requirements #

• Calculate the row-wise sum of a + b + c as totalbyrow
• Sort results by the site field (ascending or descending)
• Compute a running cumulative sum of totalbyrow (named total) after sorting
• Support the LIMIT clause to restrict results (e.g., LIMIT 15 to exclude certain rows)

Why Your Initial Query Failed #

Your original query uses t2.id <= t.id to calculate the cumulative sum, which relies on the id order instead of the sorted site order. Additionally, it doesn't sort the results by site first, so the cumulative sum doesn't align with your desired output.

Working Solution for MySQL 5.5 #

Since MySQL 5.5 doesn't support window functions, we can use user-defined variables to assign a row number based on your desired sort order, then compute the cumulative sum using that row number. Here's how to do it:

Step 1: Assign Row Numbers with Sorting

First, we create a derived table that includes the row total (totalbyrow) and assigns a sequential row number ordered by site (and id to break ties):

SELECT 
    t.*,
    (a + b + c) AS totalbyrow,
    @row_num := @row_num + 1 AS row_num
FROM 
    your_table_name t,
    (SELECT @row_num := 0) rn
ORDER BY 
    site ASC, id ASC; -- Change to DESC if you want reverse site order

Step 2: Compute Cumulative Sum

We then use this derived table to calculate the cumulative sum by summing all rows with a row number less than or equal to the current row's number. We also add support for the LIMIT clause:

SELECT 
    dt.id,
    dt.site,
    dt.a,
    dt.b,
    dt.c,
    dt.totalbyrow,
    (SELECT SUM(sub.totalbyrow) 
     FROM (
         SELECT 
             (a + b + c) AS totalbyrow,
             @rn := @rn + 1 AS row_num
         FROM 
             your_table_name,
             (SELECT @rn := 0) r
         ORDER BY 
             site ASC, id ASC
     ) sub 
     WHERE sub.row_num <= dt.row_num) AS total
FROM (
    SELECT 
        t.*,
        (a + b + c) AS totalbyrow,
        @row_num := @row_num + 1 AS row_num
    FROM 
        your_table_name t,
        (SELECT @row_num := 0) rn
    ORDER BY 
        site ASC, id ASC
) dt
-- Add your LIMIT clause here, e.g., LIMIT 2 to exclude site 60 rows
LIMIT 2;

Key Notes #

• Replace your_table_name with the actual name of your table.
• Adjust the ORDER BY clause (site ASC or site DESC) to match your desired sort order.
• Adding LIMIT at the end will restrict the results as needed (e.g., LIMIT 2 returns only the first two rows, which are the site 40 entries).
• This approach ensures the cumulative sum is calculated based on the sorted site order, not the original id order.

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