使用order by时条件选择查询不起作用

91zkwejq  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(298)

我需要简单的选择查询。在我传递一个值的条件下,当金额之和大于该值时,返回。如果我通过4,那么返回2行(在picture id->125126中),因为这2行的总和(amount)是10,比4大。
输入

输出

通过此查询,它的工作正常

SET @total:=0.0;

SELECT id,
       amount,
       price,
       @total := @total + Truncate((amount), 8) AS total_offers
FROM   table
WHERE  @total < 4;

如果我使用命令,那么它就不起作用了

SET @total:=0.0;

SELECT id,
       amount,
       price,
       @total := @total + Truncate((amount), 8) AS total_offers
FROM   table
WHERE  @total < 4
ORDER  BY price DESC;

我现在需要命令怎么解决这个问题

efzxgjgh

efzxgjgh1#

我认为自从MySQL5.6以来,您需要 order by 在子查询中使用变量。所以,这应该是可行的:

SELECT id, amount, price, total_offers
FROM (SELECT t.id, t.amount, t.price,
             @total := @total + Truncate(amount, 8) AS total_offers
      FROM (SELECT t.*
            FROM table t
            ORDER BY price DESC
           ) t CROSS JOIN
           (SELECT @total := 0) params
     ) t
WHERE total_offers < 4;

请注意,这也会更改比较以使用变量。变量的估计 WHERE 条款也是不确定的。
在mysql 8.0中,更简单的表述是:

select t.*
from (select t.*,
             sum(amount) over (order by price desc) as total_amount
      from table
     ) t
where total_amount < 4;
lhcgjxsq

lhcgjxsq2#

我想这对你应该有用。

set @total:=0.0;
SELECT temp.id, temp.amount, temp.price, 
  @total := @total + Truncate(amount, 8) AS total_offers from (
     SELECT temp.* FROM table temp ORDER BY price DESC
  ) temp
where @total < 4

相关问题