代码之家 › 专栏 › 技术社区 › Deepak M

如何返回总和(字段)<=值的行

mysql

Deepak M · 技术社区 · 8 年前

其概念是找出 sum(fCurrAmt) 可能高于输入的金额,但不应低于输入的金额。我不知道该怎么解释这个秘密。

假设我有一个桌子演示

情景:1

id fCurrAmt price
------------------
1    1      10
2    1      20
3    2      25
4    3      30

如果输入的金额是3,我需要返回前3行

id fCurrAmt price
------------------
1    1      10
2    1      20
3    2      25

在上述情况下,总和(fcurramt)为4,高于输入的金额。

情景:2

id fCurrAmt price
------------------
1    1      10
2    1      20

如果输入的金额是3,我需要返回没有记录。

在上述情况下,总和(fcurramt)为2,低于输入的金额。

我在场景1中尝试了下面的代码

SELECT a.id,a.price,a.total,a.fCurrAmt from (
  select b.id,b.price,b.fCurrAmt,(
        select sum(fCurrAmt) from demo c where c.id <= b.id order by c.id
  ) as total from demo b
) a where a.total <= 3

它只返回前两条记录

3 回复 | 直到 8 年前

Shushil Bohara 8 年前

试试这个 你需要使用 subquery 和 min 具有 group by 是的。使用 子查询 我们可以退回最低限额 id 其中 sum 对给定的数字满意,然后加入 身份证件 检索到id之前的整行

SELECT * 
FROM test t
INNER JOIN(
        SELECT MIN(id) valId 
        FROM (
            SELECT t.id,
                    (SELECT SUM(t1.fCurrAmt) 
                    FROM test t1 
                    WHERE t1.id <= t.id) AS Rowsum
            FROM test t) t2 
         WHERE Rowsum >= 3) t1 ON t1.valId >= t.id;

SQL小提琴 http://www.sqlfiddle.com/#!9/a1d07/13

Manju 8 年前

试试这个

 DECLARE @sumOfFCurrAmt int
     DECLARE @sumOfEnteredAmt int

    set @sumOfFCurrAmt=(select Sum(fCurrAmt) from demoB)
    set @sumOfEnteredAmt=(select sum(fCurrAmt) from demoC)

    IF(@sumOfFCurrAmt>@sumOfEnteredAmt)
    BEGIN
    SELECT top(@sumOfEnteredAmt)* FROM demoB 
    END

Ankur Sinha 8 年前

有点长的路,但很管用。

首先,我将fcurramt的总和存储到临时表中输入的数字。因此,前三个语句是drop、create和insert。然后,我将使用该值检查这些行的和,直到输入的数字是大是小,如果是大,然后返回所有行,直到阈值不返回任何值。这里,SOF12和场景1是同一张桌子,而SOF14和场景2中的桌子是同一张桌子。

场景1:

DROP TABLE IF EXISTS `tempsum`;

CREATE TABLE tempsum (`sum` integer(13));

INSERT INTO tempsum (SELECT SUM(fCurrAmt) FROM 
(SELECT NULL AS id, NULL AS fCurrAmt, NULL AS price, NULL AS total
FROM dual
WHERE (@total := 0)
UNION
SELECT id, fCurrAmt, price, @total := @total + fCurrAmt AS total
FROM sof12
WHERE @total <= 3) as new2);

SELECT id, fCurrAmt, price FROM (
SELECT NULL AS id, NULL AS fCurrAmt, NULL AS price, NULL AS total
FROM dual
WHERE (@total := 0)
UNION
SELECT id, fCurrAmt, price, @total := @total + fCurrAmt AS total
FROM sof12
WHERE @total <= 3) As new3 HAVING (SELECT SUM(SUM) FROM tempsum) >= 3;

本案例输出:

id   fCurrAmt   price
1      1         10
2      1         20
3      2         25

情景2:

DROP TABLE IF EXISTS `tempsum`;

CREATE TABLE tempsum (`sum` integer(13));

INSERT INTO tempsum (SELECT SUM(fCurrAmt) FROM 
(SELECT NULL AS id, NULL AS fCurrAmt, NULL AS price, NULL AS total
FROM dual
WHERE (@total := 0)
UNION
SELECT id, fCurrAmt, price, @total := @total + fCurrAmt AS total
FROM sof14
WHERE @total <= 3) as new2);

SELECT id, fCurrAmt, price FROM (
SELECT NULL AS id, NULL AS fCurrAmt, NULL AS price, NULL AS total
FROM dual
WHERE (@total := 0)
UNION
SELECT id, fCurrAmt, price, @total := @total + fCurrAmt AS total
FROM sof14
WHERE @total <= 3) As new3 HAVING (SELECT SUM(SUM) FROM tempsum) >= 3;

本例输出:未返回记录。