mysql - SQL 递归过滤结果，为每个匹配字段排除 3 个最近的记录

Question

我有数据，记录将共享一个公共(public) 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  |
----------------------------------------------

我最初尝试过类似的东西

数据库

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. 即使是，我查询中的 LIMIT 也只会返回 3 个帖子总计，而不是每个职位家长 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
                               );

关键思想是您可以将标量子查询与 < 结合使用.我想我修复了查询以执行您想要的操作，并引入了合理的表别名。