SQL递归筛选结果以排除每个匹配字段的3条最新记录

Question

我有数据，记录将共享一个公共的parent值。我想为每个parent返回最近3条记录的所有预期记录。

例如，我的数据如下所示：

----------------------------------------------
| ID     | post_date           | post_parent |
----------------------------------------------
| 326524 | 2018-08-17 14:48:09 |        1576 |
| 326693 | 2018-08-18 12:49:10 |        1576 |
| 326694 | 2018-08-18 13:04:21 |        1576 |
| 326695 | 2018-08-18 13:05:02 |        1576 |
| 326749 | 2018-08-19 19:30:33 |        1576 |
| 326752 | 2018-08-19 19:39:43 |        1576 |
| 19340  | 2011-05-28 10:38:46 |       19323 |
| 19344  | 2011-05-28 10:40:10 |       19323 |
| 19345  | 2011-05-28 10:42:12 |       19323 |
| 19347  | 2011-05-28 10:45:53 |       19323 |
| 19349  | 2011-05-28 10:49:53 |       19323 |
| 19350  | 2011-05-28 10:52:40 |       19323 |
| 19351  | 2011-05-28 10:55:45 |       19323 |
| 19352  | 2011-05-28 10:55:58 |       19323 |
| 19353  | 2011-05-28 10:57:46 |       19323 |
| 161381 | 2016-10-26 10:53:52 |       19323 |
| 161417 | 2016-10-26 12:15:56 |       19323 |
| 161418 | 2016-10-26 12:16:31 |       19323 |
| 163912 | 2016-11-10 14:24:11 |       19323 |
| 163914 | 2016-11-10 14:28:24 |       19323 |
| 163954 | 2016-11-10 16:35:51 |       19323 |
| 163956 | 2016-11-10 16:39:54 |       19323 |
| 163959 | 2016-11-10 16:43:32 |       19323 |
| 321095 | 2018-07-29 10:44:54 |       19323 |
| 321097 | 2018-07-27 17:28:09 |       19323 |
| 321135 | 2018-07-29 10:45:59 |       19323 |
| 321136 | 2018-07-29 10:57:00 |       19323 |
----------------------------------------------

最后我想：

----------------------------------------------
| ID     | post_date           | post_parent |
----------------------------------------------
| 326524 | 2018-08-17 14:48:09 |       1576  |
| 326693 | 2018-08-18 12:49:10 |       1576  |
| 326694 | 2018-08-18 13:04:21 |       1576  |
| 19340  | 2011-05-28 10:38:46 |      19323  |
| 19344  | 2011-05-28 10:40:10 |      19323  |
| 19345  | 2011-05-28 10:42:12 |      19323  |
| 19347  | 2011-05-28 10:45:53 |      19323  |
| 19349  | 2011-05-28 10:49:53 |      19323  |
| 19350  | 2011-05-28 10:52:40 |      19323  |
| 19351  | 2011-05-28 10:55:45 |      19323  |
| 19352  | 2011-05-28 10:55:58 |      19323  |
| 19353  | 2011-05-28 10:57:46 |      19323  |
| 161381 | 2016-10-26 10:53:52 |      19323  |
| 161417 | 2016-10-26 12:15:56 |      19323  |
| 161418 | 2016-10-26 12:16:31 |      19323  |
| 163912 | 2016-11-10 14:24:11 |      19323  |
| 163914 | 2016-11-10 14:28:24 |      19323  |
| 163954 | 2016-11-10 16:35:51 |      19323  |
| 163956 | 2016-11-10 16:39:54 |      19323  |
| 163959 | 2016-11-10 16:43:32 |      19323  |
| 321097 | 2018-07-27 17:28:09 |      19323  |
----------------------------------------------

一开始我试过

sql

select a.post_type
     , a.ID
     , a.post_parent
     , a.post_date
     , b.ID as parent_id
     , b.post_type as parent_post_type
     , b.post_status as parent_status 
  from wp_posts a 
  join wp_posts b 
 where a.post_type = 'revision' 
   and a.post_date > '2018-03-20' 
   and a.post_parent = b.id 
   and b.post_status = 'publish' 
   AND a.post_parent NOT IN (select post_parent 
                               from wp_posts 
                              where post_parent = 1576 
                                and post_type = 'revision' 
                              ORDER 
                                 BY post_date DESC 
                              LIMIT 3)

但意识到了两个问题：

1. 在LIMIT子句中不支持IN
2. 即使是这样，我的查询中的限制将只返回3个帖子总数，而不是每个post父级返回3个。

我假设下一步是将其分解为一个php循环并在那里执行，但我很好奇是否可以在SQL中完成这个任务！

Answer 1

您可以使用子查询来完成这一任务。。。但不使用in

select pr.post_type, pr.ID, pr.post_parent, pr.post_date,
       pp.ID as parent_id, pp.post_type as parent_post_type, pp.post_status as parent_status 
from wp_posts pr inner join
     wp_posts pp 
     on pr.post_parent = pp.id 
where pr.post_type = 'revision' and apr.post_date > '2018-03-20' and 
      pp.post_status = 'publish' and
      pr.post_date <= coalesce( (select p2.post_date
                                 from wp_posts
                                 where p2.post_parent = pr.post_parent and
                                       p2.post_type = 'revision' 
                                 order by p2.post_date desc
                                 limit 1, 2
                                ), pr.post_date
                               );

关键思想是您可以在<中使用标量子查询。我想我修正了这个查询，以便做您想做的事情，以及引入合理的表别名。

Answer 2

您可以使用窗口函数。这在MySQL v8.0中是可行的，但在早期版本中可能不行。

with temp as (
    select 
        ID,
        post_date,
        post_parent,
        row_number() over (partition by post_parent order by post_date desc) rankno
    from table_name
)

select *
from temp
where rankno > 3;