获取每个帖子的前 10 个帖子和 10 个评论 sql

本文介绍了获取每个帖子的前 10 个帖子和 10 个评论 sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想根据分数获得前 10 个帖子，并为每个帖子获得 10 条评论.我正在使用以下查询:

I want to get top 10 posts based on score, and get 10 comments for each post. I am using the following query:

with a as ( SELECt TOP 10 Score,Post.ID as PostID FROM Post order by Score desc ), b as (select PostID,ID as CommentID from PostComment) select * from a left join b on b.PostID = a.PostID

此查询获取前 10 个帖子，但问题是它获取了此帖子的所有评论.如何修改此查询以使每个帖子仅获得 10 条评论?

This query gets the top 10 posts, however the problem is that it gets all the comments for this post. how to modify this query to get only 10 comments for each post?

推荐答案

试试这个:

WITH a AS ( SELECT TOP 10 Score , Post.ID AS PostID FROM Post ORDER BY Score DESC ) SELECT * FROM a OUTER APPLY ( SELECT TOP 10 pc.PostID , pc.ID AS CommentID FROM PostComment pc WHERE pc.PostID = a.PostID --ORDER BY SomeColumn ) o