>我有这样的表格：
+---------+----------+
| post_id | reply_to |
+---------+----------+
| 1 | 0 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 5 |
| 7 | 1 |
| 8 | 7 |
| 9 | 8 |
| 10 | 7 |
+---------+----------+
reply_to只是正在回复的帖子的 ID(即 post_id of 2 是对 post_id of 1( 的答复(。
这是它放入嵌套形式时的样子：
1
2
3
5
6
4
7
8
9
10
如何创建执行以下操作的单个查询：
- 查询 1：获取回复
post_id = 1的所有帖子 (2 and 7(，并将结果数限制为 5
个查询 - 2：使用从查询 1 中检索到的值，获取子帖子(
3 and 4和8 and 10(，并将每个父帖子的结果数限制为 3
个查询 - 3：使用从查询 2 中检索到的值，获取子帖子(
5和9(，并将每个父帖子的结果数限制为 1
个
所以最后，结果应该包括这些post_ids：2, 3, 5, 4, 7, 8, 9, 10 .
这是我创建的SQL小提琴：http://sqlfiddle.com/#!2/23edc/21
请帮忙！
这在SQL Server中有效，我认为它是通用SQL，但是我现在无法让SQL Fiddle工作。
create table test1 (post_id int, reply_to int);
insert into test1 (post_id, reply_to) values
(1,0),(2,1),(3,2),(4,2),(5,3),(6,5),(7,1),(8,7),(9,8),(10,7),
(11,2),(12,2),(13,2),(14,3); /* Added records to test conditions */
/* All replies to post_id=1 */
with q1 as (
select post_id
from test1
where reply_to = 1
)
/* Top 3 replies to all results in q1 */
, q2 as (
select
q1.post_id as parent_post,
t1.post_id as child_post,
count(*) as row_num
from test1 as t1
inner join q1 on t1.reply_to = q1.post_id
left outer join test1 as t2 on t1.reply_to = t2.reply_to
and t1.post_id >= t2.post_id
group by q1.post_id, t1.post_id
having count(*) <= 3
)
/* Get 0 or 1 grandchild posts */
, q3 as (
select
q2.parent_post,
q2.child_post,
t1.post_id as grandchild_post,
count(*) as row_num
from q2
left outer join test1 as t1 on q2.child_post = t1.reply_to
left outer join test1 as t2 on t1.reply_to = t2.reply_to
and t1.post_id >= t2.post_id
group by q2.parent_post, q2.child_post, t1.post_id
having count(*) = 1
)
/* Aggregate the different post ids */
select distinct parent_post as post_id from q3
union
select distinct child_post from q3
union
select distinct grandchild_post from q3
where grandchild_post is not null;
drop table test1;