問題描述

CREATE TABLE BlogPosts 
(
PostID INT PRIMARY KEY  not null,
PostTitle NVARCHAR ,
BlogID int,
TotalComments int
)

可以使用任何 Join 而不是相關子查詢來簡化此查詢嗎?

May this query be simplified with any Join instead of correlated subquery?

SELECT TOP 5 *
FROM BlogPosts as t0
WHERE t0.PostID = (SELECT TOP 1 t1.PostID 
                FROM BlogPosts as t1
                WHERE t0.BlogID = t1.BlogID
                ORDER BY t1.TotalComments DESC)

我需要來自不同博客的 5 篇文章的總評論數上限.

I need 5 posts with max TotalComments from different blogs.

更新.SQL Server，但我更喜歡標準 SQL

UPD. SQL Server, but I would prefer standard SQL

推薦答案

如果我理解正確，postid 是唯一的，所以這應該會有所幫助

If i understand correctly, postid is unique, so this should help

好的試試這個然后

DECLARE @BlogPosts  TABLE
( 
    PostID INT PRIMARY KEY  not null, 
    PostTitle NVARCHAR , 
    BlogID int, 
    TotalComments int 
) 

INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 1, 'A', 1, 3
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 2, 'B', 1, 4
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 3, 'C', 2, 5
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 4, 'D', 2, 6
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 5, 'E', 2, 7
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 6, 'F', 1, 8
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 7, 'G', 3, 9
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 8, 'H', 4, 10
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 9, 'I', 5, 11
INSERT INTO @BlogPosts (PostID,PostTitle,BlogID,TotalComments) SELECT 10, 'J', 6, 5

SELECT  TOP 5 *
FROM    @BlogPosts bp INNER JOIN
        (
            SELECT  BlogID,
                    MAX(TotalComments) MaxComments
            FROM    @BlogPosts
            GROUP BY BlogID
        ) maxCommentsPerBlog ON bp.BlogID = maxCommentsPerBlog.BlogID
                            AND bp.TotalComments = maxCommentsPerBlog.MaxComments
ORDER BY bp.TotalComments DESC

不過，您可能有多個最大 blog-totalComments 組合.

You might have multiple max blog-totalComments combinations though.

這篇關于加入而不是相關子查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！