代码之家 › 专栏 › 技术社区 › Kevin Pullin

sql查询改进-使用max和groupby选择

max group-by select nhibernate sql-server

Kevin Pullin · 技术社区 · 15 年前

问题

给出以下两个表,我想为在给定时间范围内(例如2010年2月)发表最新(即最后)评论的帖子选择所有ID。

查询的结果应该只返回post id 1,因为post id 2的最新注释不在时间跨度筛选器的范围内。

问题

我创造了 SELECT 下面的语句似乎是正确的,并处理了向其抛出的所有测试用例。

然而,为了继续提高我的sql技能,我向社区询问是否有一种“更好”的方法可用于此场景,是否有任何关于改进现有语句的建议,以及/或未涵盖的边缘情况。

请注意,这是实际表格的松散翻译,为了使问题更容易理解而进行了更改。值得一提的是,我使用的是sql server 2005。

桌子

柱

Id    Text     Visible
1     Post 1   1
2     Post 2   1
3     Post 3   0
.     ...
n     Post n   1

Id    Post_Id    Text                  CommentNumber    Timestamp
1     1          Comment 1, Post 1     1                2/3/2010
2     1          Comment 2, Post 1     2                2/4/2010
3     2          Comment 1, Post 2     1                3/1/2010
.     .          .
n     m          Comment n, Post m     x                xx/xx/xxxx

SQL命令

SELECT [Id],[Text]
FROM [Post]  
WHERE [Id] IN (  
    SELECT comment1.[Post_Id]  
    FROM (  
        SELECT max([CommentNumber]) as maxComment,  
            [Post_id]  
        FROM [Comment]  
        GROUP BY [Post_id]  
    ) as comment2  
    INNER JOIN [Comment] as comment1 on comment1.[Post_id] = comment2.[Post_id]  
    WHERE comment1.[Timestamp] BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'  
    AND comment1.[CommentNumber] = comment2.maxComment  
)
AND [Post].[Visible] = 1

奖金问题

是否可以使用nhiberate创建此查询(使用条件api或hql)?

4 回复 | 直到 15 年前

AakashM 15 年前

SELECT
    Post_Id
FROM
    Comment
GROUP BY
    Post_Id
HAVING
    MAX(Timestamp) >= '2/1/2010'

无可指责 HAVING 作为一个 WHERE 发生了什么之后 GROUP BY ,操作 分组的 结果集。

但我不知道NHibernate。

Justin 15 年前

好的解决方案已经发布,但是我想我会发布一个关于如何逐步简化查询的解释:

最外层的子查询是多余的

子查询的最外层( SELECT [Id] FROM [Post] WHERE [Id] IN ( 是多余的,因为您已经返回了一个id列表)。

这就留给我们

SELECT comment1.[Post_Id]
FROM (  
    SELECT max([CommentNumber]) as maxComment,  
        [Post_id]  
    FROM [Comment]  
    GROUP BY [Post_id]  
) as comment2  
INNER JOIN [Comment] as comment1 on comment1.[Post_id] = comment2.[Post_id]  
WHERE comment1.[Timestamp] BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'  
AND comment1.[CommentNumber] = comment2.maxComment

使用commentNumber是多余的

不需要使用commentNumber来获取最新的评论,因为帖子已经按时间戳排序。这意味着,与其选择具有最高id的注释的时间戳,不如只选择最高的时间戳。

这样就不需要再次加入评论,只需要:

SELECT [Post_Id], SomeColumn, SomeOtherColumn
FROM (
    SELECT max([TimeStamp]) as maxTimeStamp,
        [Post_id],
        SomeColumn,
        SomeOtherColumn
    FROM [Comment]
    GROUP BY [Post_id]
) as GroupedComments
WHERE GroupedComments.maxTimeStamp BETWEEN '2/1/2010 00:00:00.000' AND '2/28/2010 23:59:59.999'

子查询现在是多余的

现在查询已经被简化了,应该很容易看出如何使用 distinct 或 having 语法。

使用<和>=而不是介于两者之间

只是个小麻烦。与其不遗余力地查找2月份的最后一个日期,不如将两者分割为一个<和一个>=使查询更加清晰:

WHERE GroupedComments.maxTimeStamp >= '2/1/2010'
AND GroupedComments.maxTimeStamp < '3/01/2010'

a'r 15 年前

这应该比使用having子句快一点。

select distinct Post_id from Comment
where Timestamp >= '2/1/2010';

Kevin Pullin 15 年前

这是我在综合了Aakashm和Kragen的回答后,目前针对的问题:

SELECT [Id],[Text]
From [Post]
WHERE [Id] IN (
    SELECT Post_Id
    FROM Comment
    GROUP BY Post_Id
    HAVING MAX(Timestamp) >= '3/1/2010' AND MAX(Timestamp) < '4/1/2010'
)
AND [Post].[Visible] = 1

下面是如何使用条件api在nhibernate中表示此查询:

var subCriteria = DetachedCriteria.For<Comment>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Post.Id")))
    .Add(Restrictions.Ge("Timestamp", new DateTime(2010, 3, 1)))
    .Add(Restrictions.Lt("Timestamp", new DateTime(2010, 4, 1)));

var criteria = session.CreateCriteria<Post>()
    .Add(Restrictions.Eq("Visible", true))
    .Add(Subqueries.PropertyIn("Id", subCriteria));