查询获取所有记录，直到列的总和小于或等于一个值 [英] Query to get all records until the sum of column less than or equal a value

问题描述

我想获取表中的所有记录，直到特定列的总和小于或等于n".

例如:

表格:数据

<前>slno 商品价格1 件 1 10002 项 2 20003 项 3 30004 项 4 40005 项 5 50006 项 6 6000

我想从上表中获取所有记录，直到总和(价格)小于或等于 10000

因此，对于上表，我的结果将是前 4 条记录.

解决方案

你可以做

SELECT slno, item, price从(SELECT slno, item, price, @t := @t + price totalFROM table1 CROSS JOIN (SELECT @t := 0) i按 slno 订购) qWHERE 总数 <= 10000

或

SELECT slno, item, price从(选择 slno，项目，价格，(选择总和(价格)从表 1哪里 slno <= t.slno) 全部的FROM table1 t) qWHERE 总数 <= 10000按 slno 订购

这是SQLFiddle演示

I want to get all the records in a table until the sum of a particular column is less than or equal to 'n'.

For example:

Table: data

slno     item       price

1        item1      1000
2        item2      2000
3        item3      3000
4        item4      4000
5        item5      5000
6        item6      6000

I want to get all records from above table until the sum(price) is less than or equal to 10000

So, with the above table my result would be first 4 records.

解决方案

You can do

SELECT slno, item, price
  FROM
(
  SELECT slno, item, price, @t := @t + price total
    FROM table1 CROSS JOIN (SELECT @t := 0) i
   ORDER BY slno
) q
 WHERE total <= 10000

or

SELECT slno, item, price
  FROM
(
  SELECT slno, item, price, 
  (
    SELECT SUM(price)
      FROM table1
     WHERE slno <= t.slno
  ) total
    FROM table1 t
) q
 WHERE total <= 10000
 ORDER BY slno

Here is SQLFiddle demo

这篇关于查询获取所有记录，直到列的总和小于或等于一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！