T-SQL - 帮助MAX操作多对多

这与我提出的上一个问题密切相关。 Post和Location之间有多对多的关系。连接表称为PostLocations，除了FK之外什么都没有。（LocationId，PostId）我正试图拉回每个位置的顶部帖子。这是我的查询（在我之前的问题的答案中给出）：

SELECT pl.LocationId, p.postid, p.UniqueUri, p.Content, MAX(s.BaseScore) as topscore
from dbo.PostLocations pl
inner join dbo.posts p on pl.PostId = p.PostId
inner join dbo.reviews r on p.postid = r.postid
inner join dbo.scores s on r.scoreid = s.scoreid
group by pl.locationid, p.postid, p.UniqueUri, p.Content

但问题是，因为PostLocations可能有这样的条目：

LocationId   PostId
1            213213
2            498324
1            230943

所以我的上面的查询是两次返回LocationId 1，因为它在连接表中有两个记录。我只想要每个位置1条记录 - 每个locationid的最高帖子。我也试过这个：

SELECT l.LocationId, p.postid, p.UniqueUri, p.Content, MAX(s.BaseScore) as topscore
from dbo.PostLocations pl
inner join dbo.Locations l on pl.LocationId = l.LocationId
inner join dbo.posts p on pl.PostId = p.PostId
inner join dbo.reviews r on p.postid = r.postid
inner join dbo.scores s on r.scoreid = s.scoreid
group by l.locationid, p.postid, p.UniqueUri, p.Content

相同的结果 - 这是回来的：

LocationId   PostId   UniqueUri   Content   TopScore
1            213213   some-post   pew pew   2.00
2            498324   anot-post   blah bl   4.50
1            230943   sadjsa-as   asijd a   3.5

这是应该回来的：

LocationId   PostId   UniqueUri   Content   TopScore
1            230943   sadjsa-as   asijd a   3.5
2            498324   anot-post   blah bl   4.50

因为LocationId 1有2个帖子，但PostId 230943的得分最高，因此返回了一个。关于我缺少什么的任何想法？

已邀请:

2 个回复

扇献隙

如果您使用的是SQL Server 2005或更高版本，则可以执行以下操作：

With RankedLocations As
    (
    Select PL.LocationId
        , S.BaseScore
        , P.PostID
        , P.UniqueUri
        , P.Content
        , Row_Number() Over( Partition By PL.LocationId Order By S.BaseScore Desc ) As ScoreRank
    From dbo.PostLocations As PL
        Join dbo.Posts As P
            On P.PostId = PL.PostId
        Join dbo.Reviews As R
            On R.PostId = P.PostId
        Join dbo.Scores As S
            On S.ScoreId = R.ScoreId
    )
Select LocationId, BaseScore, PostID, UniqueUri, Content
From RankedLocations
Where ScoreRank = 1

贡炮逗握惫

当你在postID上进行分组时，每个帖子都是独一无二的。我不确定是否有更好的方法来做到这一点，但我过去所做的就是沿着这些方向运行

Select l.LocationId, p.postid, p.UniqueUri, p.Content, s.basescore as topscore
from
    dbo.Locations l inner join
    (select 
        pl.locationid, max(s.BaseScore) as topscore
    from
        dbo.postlocations pl 
        inner join dbo.posts p on pl.PostId = p.PostId
        inner join dbo.reviews r on p.postid = r.postid
        inner join dbo.scores s on r.scoreid = s.scoreid
    group by
        pl.locationid) as topPost on l.locationid = topPost.locationid
    inner join dbo.postlocations pl on pl.locationid = l.locationid
    inner join dbo.posts p on pl.PostId = p.PostId
    inner join dbo.reviews r on p.postid = r.postid
    inner join dbo.scores s on r.scoreid = s.scoreid and s.basescore = toppost.topscore

我们创建一个子查询来查找给定位置的最高分，然后像之前和最后一个连接那样进行连接，确保基本分数是我们之前找到的最高分。这意味着如果我们对给定位置有两个相等的最高分，我们将返回两个行，但是在所有其他情况下，我们将返回每个位置的单个行，可以修改它以选择两个相等的最高分的任意帖子但是我还没有这样做。我有兴趣看看这个问题是否有任何其他解决方案，因为额外连接的数量，这是解决问题的计算上非常昂贵的解决方案。编辑 - 响应您的评论，因为postid是主键，我们可以依赖它作为最新帖子的最佳选择。

Select l.LocationId, p.postid, p.UniqueUri, p.Content, bar.basescore as topscore
from
    dbo.Locations l inner join
    (select 
        l.LocationId, max(p.postid) as postid ,max(s.basescore) as basescore
    from
        (select 
            pl.locationid, max(s.BaseScore) as topscore
        from
            dbo.postlocations pl 
            inner join dbo.posts p on pl.PostId = p.PostId
            inner join dbo.reviews r on p.postid = r.postid
            inner join dbo.scores s on r.scoreid = s.scoreid

        group by
            pl.locationid) as topPost on l.locationid = topPost.locationid
        inner join dbo.postlocations pl on pl.locationid = l.locationid
        inner join dbo.posts p on pl.PostId = p.PostId
        inner join dbo.reviews r on p.postid = r.postid
        inner join dbo.scores s on r.scoreid = s.scoreid and s.basescore = toppost.topscore
    group by l.locationid) as bar on l.locationid = bar.locationid
    inner join posts p on bar.postid = p.postid

要回复问题请先登录或注册

T-SQL - 帮助MAX操作多对多

2 个回复

发起人

问题状态

T-SQL - 帮助MAX操作多对多

与内容相关的链接

2 个回复

发起人

问题状态