选择所有行，直到日期差异条件与分组匹配 [英] select all the rows until date difference condition match with grouping

问题描述

参考这个问题

选择直到日期差异的所有行距离底部是 4

解决方案效果很好，但如何按 customer_id 分组以及按 4 天差异和 8 天差异对每个 customer_id 组进行分组

Solution works great but how to group by customer_id and for each customer_id group by 4 day diff, 8 day diff

http://sqlfiddle.com/#!2/93d75/3

推荐答案

您只需要跟踪最新的 customer_id 并对其执行okay"逻辑:

You just need to keep track of the latest customer_id and perform the 'okay' logic on that too:

SELECT   amount, customer_id, updated_at, date_diff
FROM (
  SELECT   *,
           @b:=NOT @c<=>customer_id
               OR (@b AND DATEDIFF(@t,updated_at)<=date_diff) AS okay,
           @c:=customer_id,
           @t:=updated_at
  FROM     my_table, (SELECT 4 AS date_diff) init
  ORDER BY customer_id, updated_at DESC
) t
WHERE    okay
ORDER BY customer_id, updated_at, id

在 sqlfiddle 上查看.

这篇关于选择所有行，直到日期差异条件与分组匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！