选择所有行直到日期差异从底部开始为 4 [英] select all the rows until date diff is 4 from bottom

问题描述

我想从表中选择所有行，其中两行之间的日期不同为 4 或更少，并在条件不匹配时立即停止，即 diff > 4 按降序排列，即(从底部)

I want to select all the rows from table where date different between two row is 4 or less and stop as soon as condition don't match ie diff > 4 in descending order i.e.(from bottom)

注意:一旦条件> 4，它就不会再匹配了停止

caution: once condition is > 4 it will not match further it simply stops

即它必须选择 5,6,7,8,9

i.e it will have to select 5,6,7,8,9

我尝试使用HAVING"，但它在大数据上很慢

I tried using "HAVING", but its slow on big data

提前感谢大家帮助解决问题.

Thank you all in advance to helping figure it out.

推荐答案

您可以使用 user-defined variables 跟踪先前的 updated_at 值(在我下面的示例中，在 @t 中)，然后根据该值计算是否需要记录是否需要前一条记录 (@b)，如果需要，最后日期与当前日期之间的差异:

You can use user-defined variables to track the previous updated_at value (in my example below, in @t) then calculate from that whether the record is desired based on whether the previous record was desired (@b) and, if so, the difference between that last date and the current one:

SELECT   id, amount, updated_at
FROM (
  SELECT   my_table.*,
           @b:=IF(@b, DATEDIFF(@t,updated_at)<=4, FALSE) okay,
           @t:=updated_at
  FROM     my_table, (SELECT @b:=TRUE, @t:=MAX(updated_at) FROM my_table) init
  ORDER BY updated_at DESC
) t
WHERE    okay
ORDER BY updated_at, id

在 sqlfiddle 上查看.

这篇关于选择所有行直到日期差异从底部开始为 4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！