如何在条件持续的情况下在Mysql中选择行 [英] how to select lines in Mysql while a condition lasts

问题描述

我有这样的东西:

 Name.....Value
 A...........10 
 B............9
 C............8

意思是，值按降序排列.我需要创建一个新表，该表将包含占总值60％的值.因此，这可能是伪代码:

Meaning, the values are in descending order. I need to create a new table that will contain the values that make up 60% of the total values. So, this could be a pseudocode:

set Total = sum(value) 
set counter = 0 
foreach line from table OriginalTable do: 
counter = counter + value 
if counter > 0.6*Total then break
else insert line into FinalTable
end

如您所见，我在这里解析sql行.我知道可以使用处理程序来完成此操作，但是我无法使其正常工作.因此，任何使用处理程序或其他创意的解决方案都是不错的选择. 还应该在合理的时间复杂度内-解决方案

As you can see, I'm parsing the sql lines here. I know this can be done using handlers, but I can't get it to work. So, any solution using handlers or something else creative will be great. It should also be in a reasonable time complexity - the solution how to select values that sum up to 60% of the total works, but it's slow as hell :(
Thanks!!!!

推荐答案

您可能需要使用lead()或lag()

You'll likely need to use the lead() or lag() window function, possibly with a recursive query to merge the rows together. See this related question:

合并DATE-如果情节是直接连续或重叠，则排成一行

如果您使用的是MySQL，则可以使用以下方法来解决缺少窗口功能的问题:

And in case you're using MySQL, you can work around the lack of window functions by using something like this:

MySQL查询问题

这篇关于如何在条件持续的情况下在Mysql中选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！