将行限制为列的总和等于MySQL中的某个值

I want to write a query which returns all rows until the sum of one of the columns value reaches a certain value.

我想编写一个返回所有行的查询，直到其中一列值的总和达到某个值。

For example in the table below:

例如，在下表中：

           DATE             ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4
    2013-02-03 00:00:00    78927        6
    2013-02-04 00:00:00    85662        2
    2013-03-05 00:00:00    47978        1
    2013-08-07 00:00:00     8582        1

If I want to get the rows until the sum of column Meeting equals 7.

如果我想获取行，直到会议列的总和等于7。

           DATE             ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4

If I want to get the rows until the sum of column Meeting equals 13.

如果我想获取行，直到会议列的总和等于13。

     DATE                   ETC      Meeting
    2013-02-01 00:00:00    85482        1
    2013-02-01 00:00:00    47228        2
    2013-02-02 00:00:00    12026        4
    2013-02-03 00:00:00    78927        6

8 个解决方案

#1

Here's a way which should work in MySQL :

这是一种适用于MySQL的方法：

SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  (SELECT
     sum(MyAmountCol) FROM Table1
   WHERE Id <= O.Id) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <= 7

It involves calculating a running total and selecting records while the running total is less than or equal to the given number, in this case 7.

它涉及计算运行总计并选择记录，而运行总数小于或等于给定数量，在这种情况下为7。

SQL Fiddle

#2

Here's a way to do it without a stored procedure:

这是一种没有存储过程的方法：

SET @msum := 0;
SELECT t1.* 
FROM (
    SELECT m.*,  
          (@msum := @msum + m.meetings) AS cumulative_meetings
    FROM meetings m 
    ORDER BY m.date ASC
) t1 
WHERE t1.cumulative_meetings <= 7;

#3

This one outperforms all.

这个优于所有人。

SET @runningTotal=0;
SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  @runningTotal + O.MyAmountCol as 'RunningTotal',
  @runningTotal := @runningTotal + O.MyAmountCol
FROM Table1 O
HAVING RunningTotal <=7;

SQL Fiddle

SQL小提琴

Take a look at execution plans for both queries.

查看两个查询的执行计划。

#4

Hemant you do not state the RDBMS that use. Here is a script in t-sql that you can use in order to solve your problem.

Hemant你没有陈述使用的RDBMS。这是t-sql中的一个脚本，您可以使用它来解决您的问题。

DECLARE @numberToReach INT;
SET @numberToReach = 10; --you can change this

DECLARE @date DATETIME;
DECLARE @etc VARCHAR(20);
DECLARE @meeting INT;
DECLARE @temp_sum INT;

CREATE TABLE #tempTable
    (
        Dates DATETIME,
        Etcs VARCHAR(20),
        Meeting INT,
    )

DECLARE tempcursor CURSOR FOR
        SELECT *
        FROM YourTABLENAME
OPEN tempcursor;
FETCH NEXT FROM tempcursor INTO @date, @etc, @meeting;

WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @temp_sum = @temp_sum + @meeting;
    IF @temp_sum < @numberToReach 
    BEGIN
        INSERT INTO #tempTable
        (
            Dates,
            Etcs,
            Meeting
        )
        VALUES
        (
            @date, 
            @etc, 
            @meeting
        )

        FETCH NEXT FROM tempcursor INTO @date, @etc, @meeting;
    END 
END

SELECT * FROM #tempTable

CLOSE tempcursor
DEALLOCATE tempcursor

DROP TABLE  #tempTable

#5

Here's an ugly way:

这是一个丑陋的方式：

SELECT *
FROM meetings m1
WHERE (SELECT SUM(m2.Meeting) 
       FROM meetings m2 
       WHERE m2.DATE < m1.DATE OR (m2.DATE = m1.DATE AND m2.ETC >= m1.ETC)) <= 7

The ordering is based on DATE first, then ETC in descending order, since that seems to be what we have to go on. Note that if that is not unique, you will get the wrong result.

排序首先基于DATE，然后是降序的ETC，因为这似乎是我们必须继续进行的。请注意，如果这不是唯一的，您将得到错误的结果。

#6

In Oracle, I think you can use the SUM analytic function. You can refer here: http://www.adp-gmbh.ch/ora/sql/analytical/sum.html

在Oracle中，我认为您可以使用SUM分析功能。您可以在这里参考：http：//www.adp-gmbh.ch/ora/sql/analytical/sum.html

#7

As no DBMS is given, the following is an ANSI SQL solution which works on a wide range of DBMS:

由于没有给出DBMS，以下是适用于各种DBMS的ANSI SQL解决方案：

select *
from (
    select date_column, 
           etc, 
           sum(Meeting) over (order by date_column asc) run_sum
    from meetings
) t
where run_sum <= 7
order by date_column asc;

(I used the column name date_column instead of date because DATE is a reserved word in SQL and should not be used as a column name to avoid problems)

（我使用了列名date_column而不是date，因为DATE是SQL中的保留字，不应该用作列名以避免出现问题）

#8

If you are using SQL Server, then use CROSS APPLY.

如果您使用的是SQL Server，请使用CROSS APPLY。