代码之家 › 专栏 › 技术社区 › MatÄj Å tágl

查询中的SQL CTE作用域

common-table-expression sql-server sql

MatÄj Å tágl · 技术社区 · 5 年前

我有桌子 ChatMessages , ChatGroups 和 ChatGroupMemberships 。用户可以在0..N个组中,组中可以有1..N条聊天消息。第一条消息是在组启动后创建的,它有点像“活”ping。

我正在优化重构用户对话列表的方式。这个列表很标准,你可以从任何社交网站上知道:

| Chat with User X -> [last message in that chat group]
| Group chat named ABC -> [same]

到目前为止,我所做的只是查询了一份清单 ChatGroup会员资格 哪里 userId = X (x是登录用户),然后对于此集合中的每个条目,我选择了该组中的最新消息,然后在服务器上对整个列表进行了排序(而不是在数据库上进行排序)。

在这个项目中,我尽可能地使用NHibernate创建查询,因此上述函数如下:

public ChatMessage GetLastMessageInGroup(int groupId)
{
    return session.CreateCriteria<ChatMessage>()
    .AddOrder(Order.Desc("Date"))
    .Add(Restrictions.Eq("RecipientId", groupId))
    .SetMaxResults(1)
    .List<ChatMessage>().FirstOrDefault();
 }

现在我很确定,这是我在那里做的一个相当丑陋的解决方案,随着用户创建越来越多的聊天组,对话列表的重建时间开始花费越来越多的时间(我是通过AJAX这样做的,但仍然如此)。现在,用户通常是大约50个组的成员。

因此,需要对其进行优化,我现在要做的是将该列表的加载拆分为更小的批。当用户滚动列表时,会动态加载条目。

我正在处理的函数看起来像:

public List<ChatGroupMembershipTimestamp> GetMembershipsWhereUserId(int userId, int take, int skip)
{

}

我花了几个小时学习CTE,得出了以下结论:

WITH ChatGroupMemberships AS
( 
    SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.userId
    ROW_NUMBER() OVER(PARTITION BY p.RecipientId, p.userId ORDER BY p.Id DESC)
    AS rk FROM ChatMessages p
)

SELECT s.date, s.recipientId as groupId, s.recipientType as groupType, s.userId
FROM ChatGroupMemberships s

WHERE s.rk = 1 
Order by s.date desc;

这将返回每个组中每个用户的最后(最新)消息。问题是,我的用户不是每个组的成员,所以我需要以某种方式 加入? 这个在 ChatGroup会员资格 表,并检查是否存在具有该用户ID的行 userId

上述查询的示例如下:

我可能过于复杂了,我最初的计划是执行:

select m.id as messageId, groupId, date
from ChatGroupMemberships g 
join ChatMessages m on g.groupId = m.recipientId
where g.userId = XXX <-- user's id
order by m.date desc

产量:

但在这里,我只需要我在上面的查询中尝试的每个groupId的最上面一行。很抱歉,这可能会令人困惑(由于我缺乏适当的术语/知识),而且这是一个相当长的问题。

如果需要任何澄清,我非常乐意合作。

最后,我附上了表格的设计方案

聊天消息:

聊天组成员资格:

聊天组:

0 回复 | 直到 5 年前

AlexYes 5 年前

WITH messages_ranked AS
( 
    SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.userId
    ROW_NUMBER() OVER(PARTITION BY p.RecipientId, p.userId ORDER BY p.Id DESC) AS rk 
    FROM ChatMessages p
    JOIN ChatGroupMemberships g 
    on p.recipientId = g.groupId
    where g.user_id = XXX
)

SELECT s.date, s.recipientId as groupId, s.recipientType as groupType, s.userId
FROM messages_ranked s

WHERE s.rk = 1 
Order by s.date desc;

pwilcox 5 年前

我看不到你的照片,但如果你的计划只是制作你的“原始计划”的最新版本,那么它应该是这样的:

with chatMessages as ( 

    select  m.*,

            rk = row_number() over(
                partition by m.recipientid, m.userid 
                order by m.id desc
            )

    from    chatMessages m

)

select      m.id as messageId, g.groupId, m.date
from        chatGroupMemberships g 
join        chatMessages m on g.groupId = m.recipientId and m.rk = 1
where       g.userId = XXX <-- user's id
order by    m.date desc

重点是,你不要试图去做 chatGroupMemberships 由于 chatMessages 。您只需对进行所需的筛选 聊天消息 然后像以前一样使用它。

话虽如此。如果你担心的是“我的用户不是所有这些组的成员”,我不知道你的原始查询在任何意义上对你来说是如何工作的。

根据你的c#代码, .Add(Restrictions.Eq("RecipientId", groupId)) ,我想知道你是否需要换衣服

g.userId = XXX

到

m.recipientId = XXX

无论如何,为什么 g.groupId 匹配到 m.recipientId 这似乎是个错误,如果不是,那也很奇怪。