MySQL选择总和大于阈值的记录 [英] MySQL select records with sum greater than threshold

问题描述

我需要基于MySQL数据库表中列出的文件大小来选择记录，并使用单个查询(无存储过程).记录集应包含文件总和等于或必要时超过特定阈值的所有记录. (例如，阈值= 30，结果返回3条记录，文件大小等于10、10、20或10、10、10或一条记录，文件大小为32)

I need to select records based on file size listed in a MySQL database table, with a single query (no store procedures). The record set should contain all records where the sum total file size equals or if needed exceeds a specific threshold. (Example, threshold = 30, results return 3 records with file sizes equal to 10, 10, 20 or 10, 10, 10 or one record with a file size of 32)

表

+----+---------+-----------+
| id | user_id | fileSize  |
+----+---------+-----------+
|  1 |       1 |      9319 |
|  2 |       1 |     51683 |
|  3 |       1 |     19776 |
|  4 |       1 |    395890 |
|  5 |       1 |      7132 |
|  6 |       1 |     97656 |
|  7 |       1 |      9798 |
|  9 |       1 |     16096 |
| 10 |       1 |    113910 |
| 11 |       1 |    160037 |
+----+---------+-----------+

在研究并尝试了许多不同的解决方案之后，我提出的最佳查询如下:

After researching and trying a number of different solutions the best query that I have come up with looks like this:

SELECT f1.user_id, f1.id AS file_id, f1.fileSize, SUM(f2.fileSize) AS totalSum
FROM files AS f1 
INNER JOIN files AS f2 ON f1.id >= f2.id 
WHERE f1.user_id = 1
GROUP BY f1.id 
HAVING totalSum <= 350000;

示例结果

+---------+---------+-----------+----------+
| user_id | file_id |  fileSize | totalSum |
+---------+---------+-----------+----------+
|       1 |       1 |      9319 |     9319 |
|       1 |       2 |     51683 |    61002 |
|       1 |       3 |     19776 |    80778 |
+---------+---------+-----------+----------+

所需结果

+---------+---------+-----------+----------+
| user_id | file_id |  fileSize | totalSum |
+---------+---------+-----------+----------+
|       1 |       1 |      9319 |     9319 |
|       1 |       2 |     51683 |    61002 |
|       1 |       3 |     19776 |    80778 |
|       1 |       4 |    395890 |   476668 |
+---------+---------+-----------+----------+

或

+---------+---------+-----------+----------+
| user_id | file_id |  fileSize | totalSum |
+---------+---------+-----------+----------+
|       1 |       3 |    395890 |   395890 |
+---------+---------+-----------+----------+

上面的查询无法正常工作的是，永远不会满足阈值，因为它基于小于阈值的HAVING(大于仅返回远远超出阈值的疯狂记录).另外，如果集合中有任何记录的文件大小超过阈值，则查询结果有时会返回空.理想结果将达到或稍微超过阈值，并且如果单个文件大小匹配或超过阈值，则可能包含许多记录或单个记录.

What isn't working with the query above is that the threshold will never be met, as it is based on HAVING lesser than the threshold (greater than just returns crazy amounts of records well above the threshold). Also, if there are any records in the set that have a file size exceeding the threshold, the query result sometimes returns empty. Ideal results would meet or slightly exceed the threshold and may contain many records or a single record if the single file size matched or exceeded the threshold.

任何帮助将不胜感激.我认为这是五年来我第一次在网上发布问题.认真地，坚持了一个星期. 〜谢谢

Any help would be appreciated. I think this is the first time I have posted a question online in about five years. Seriously, been stuck on this for a week. ~ Thx

推荐答案

这似乎适合UNION结果集.因此，您必须获得2个查询(每个条件"一个)，并使用并集将其结果合并.

This seems to be fitted for a UNION resultset. So you have to get 2 queries (one for each "criteria") and join their result using union.

第一个查询将变为:

SELECT f1.user_id, f1.id AS file_id, SUM(f1.fileSize) AS totalSum
FROM files AS f1 
WHERE f1.user_id = 1
GROUP BY f1.id 
HAVING totalSum <= 350000;

现在，您需要选择大小是否过大的查询:

Now you need the query that select if size is too big:

SELECT f1.user_id, f1.id AS file_id, MAX(f1.fileSize) AS max
FROM files AS f1 
WHERE f1.user_id = 1
GROUP BY f1.id 
HAVING max >= 350000;

接下来，您要将它们合并在一个查询中.由于两者具有相同的字段，因此您可以简单地合并"结果

Next you want to combine them in a single query. Since both have the same fields you can simply "union" the result

SELECT f1.user_id, f1.id AS file_id, SUM(f1.fileSize) AS totalSum
FROM files AS f1 
WHERE f1.user_id = 1
GROUP BY f1.id 
HAVING totalSum <= 350000
UNION
SELECT f1.user_id, f1.id AS file_id, MAX(f1.fileSize) AS max
FROM files AS f1 
WHERE f1.user_id = 1
GROUP BY f1.id 
HAVING max >= 350000;

PS:您将"ON f1.id> = f2.id"作为加入条件，不确定为什么>会非常区分大小写:)

PS: You had "ON f1.id >= f2.id" as join criteria, not sure why the > that would be very case-specific :)

这篇关于MySQL选择总和大于阈值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！