如何在条件下找到Sum(field)，即“从表中选择*，其中sum(field)< 150英寸 [英] how to find Sum(field) in condition ie "select * from table where sum(field) < 150"

问题描述

我只需要检索大小字段总和为< = 150的特定记录. 我有下面的表格...

I have to retrieve only particular records whose sum value of size field is <=150. I have table like below ...

userid size
1       70
2      100   
3       50
4       25
5      120
6       90

输出应为...

userid size
1       70
3       50
4       25

例如，如果我们加上70,50,25，我们得到145，即< = 150.

For example, if we add 70,50,25 we get 145 which is <=150.

我该如何编写查询来完成此任务?

How would I write a query to accomplish this?

推荐答案

下面的查询将产生以上结果:

Here's a query which will produce the above results:

SELECT * FROM `users` u
WHERE (select sum(size) from `users` where size <= u.size order by size) < 150
ORDER BY userid

但是，您描述的想要选择最适合给定大小的用户的问题是 NP-Hard 问题，无法通过以下方式轻松解决ANSI SQL.但是，以上内容似乎返回了正确的结果，但实际上，它只是从最小的项目开始，然后继续添加项目，直到垃圾箱已满.

However, the problem you describe of wanting the selection of users which would most closely fit into a given size, is a bin packing problem. This is an NP-Hard problem, and won't be easily solved with ANSI SQL. However, the above seems to return the right result, but in fact it simply starts with the smallest item, and continues to add items until the bin is full.

一种通用，更有效的装箱算法是从最大的物品开始，并在适合时继续添加较小的物品.该算法将选择用户5和4.

A general, more effective bin packing algorithm would is to start with the largest item and continue to add smaller ones as they fit. This algorithm would select users 5 and 4.

这篇关于如何在条件下找到Sum(field)，即“从表中选择*，其中sum(field)&lt; 150英寸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！