SQL:如何选择总和为特定值的行 [英] SQL: How to select rows that sum up to certain value

问题描述

我想选择总和为特定值的行.

I want to select rows that sum up to a certain value.

我的 SQL(SQL Fiddle):

id  user_id     storage
1   1           1983349
2   1           42552
3   1           367225
4   1           1357899
37  1           9314493

它应该计算到 410000 的总和并得到行.同时它应该是这样的:

It should calculate the sum up to 410000 and get the rows. Meanwhile it should get something like this:

id  user_id     storage
2   1           42552
3   1           367225

如您所见，42552 + 367225 = 409777.它选择了接近 410000 的两行.

As you can see, 42552 + 367225 = 409777. It selected two rows that are nearly 410000.

我已经尝试了一切，但没有奏效:(

I have tried everything but it didn't work :(

抱歉我的语言，我是德国人.

Sorry for my language, I am German.

推荐答案

您可以使用相关子查询来获取运行总数并检索那些运行总数小于等于的行.一个指定的数字.(请注意，我将存储列更改为 int.如果它是 varchar，则比较将返回错误结果)

You can use a correlated subquery to get the running total and retrieve those rows whose running total is < a specified number. (note that i changed the storage column to int. if it is a varchar the comparison would return the wrong result)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage),0) < 410000
order by storage

SQL Fiddle

当存储列中存在重复值时，必须通过为 id 列包含一个条件来将其计入运行总和.(在这种情况下，< 条件已被使用，因此会选取重复存储值的最小 id)

When there are duplicate values in the storage column, it has to be accounted for in the running sum by including a condition for the id column. (in this case < condition has been used, so the smallest id for a duplicate storage value gets picked up)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage 
                        or (storage=t.storage and id < t.id)),0) < 410000
order by storage

这篇关于SQL:如何选择总和为特定值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！