如何在条件持续的情况下在Mysql中选择行

如何在条件持续的情况下在Mysql中选择行

本文介绍了如何在条件持续的情况下在Mysql中选择行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有这样的东西:

 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 totalworks, 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中选择行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-01 17:55