主页 / coding / 问题 / 77707092
Accepted
PixelPaul
Asked: 2023-12-23 16:45:16 +0800 CST

聊天应用程序的 SQL 查询

  • 772

我真的很难理解如何编写查询。聊天功能有两个主要表格。该UserConversation表保存双向聊天的发件人和收件人 ID。该UserMessage表保存相应的消息。需要 和 表只是为了从中提取一些额外的详细信息User。UserProfile这是表结构：

用户对话

ID 发件人ID 发件人删除日期 收件人ID 收件人删除日期 创建日期
1 1002 无效的 1001 无效的 2023-12-23 14:14:13.1152723 +07:00
2 1001 无效的 1003 无效的 2023-12-23 14:15:20.1264302 +07:00
3 1001 无效的 1004 无效的 2023-12-23 14:16:57.4302621 +07:00

用户

ID 用户名
1001 约翰·多伊
1002 弯腰
1003 杰克·史密斯
1004 克里斯·克林格尔

用户资料

用户身份 阿凡达
1001 头像/图片/1001.jpg
1002 头像/图片/1002.jpg
1003 头像/图片/1003.jpg
1004 头像/图片/1004.jpg

用户留言

会话ID 发件人ID 收件人ID 留言已读 信息 创建日期
1 1002 1001 0 你好 2023-12-22 13:00:00
1 1001 1002 0 你好呀 2023-12-22 13:30:00
2 1001 1003 1 圣诞节快乐 2023-12-22 14:00:00
2 1003 1001 1 你也一样 2023-12-22 14:30:00
3 1001 1004 1 喝点啤酒吗？ 2023-12-22 15:00:00
3 1004 1001 0 当然 2023-12-22 15:30:00

我正在尝试实现以下输出：

会话ID 用户身份 用户名 用户头像 最新日期 未读计数
3 1004 克里斯·克林格尔 头像/图片/1004.jpg 2023-12-22 15:30:00 1
2 1003 杰克·史密斯 头像/图片/1003.jpg 2023-12-22 14:30:00 0
1 1002 弯腰 头像/图片/1002.jpg 2023-12-22 13:30:00 1

一些注意事项：

  1. 在表中搜索或 的出现UserConversation次数，但我想返回其他用户的用户详细信息。JohnDoeSenderIdRecipientId
  2. MessageRead表中每个对话的计数列UserMessages，但仅限于搜索到的用户 ( JohnDoe) 是消息收件人的情况。
  3. 按最近的结果排序CreateDate

我还没有走得太远，这就是我到目前为止所拥有的。

select 
    uc.Id as Id,
    us.Id as UserId,
    us.UserName as Username,
    ups.AvatarImage as UserAvatar
from UserConversation uc 
join UserMessage um on uc.Id = um.ConversationId
join [User] us on uc.SenderId = us.Id
join [User] ur on uc.RecipientId = ur.Id
join UserProfile ups on us.Id = ups.UserId
join UserProfile upr on ur.Id = upr.UserId
where uc.SenderId = 1001 or uc.RecipientId = 1001
sql

1 个回答

  1. Best Answer

    由于您希望 JohnDoe 的行与其他参与者一起作为发送者或接收者，因此您应该将 User 和 UserProfile 表连接两次（一次用于发送者，另一次用于接收者）。完成此操作后，您可以进行聚合和分组以及一些 case 表达式（或 Decode() 函数或其他类似的函数），以获取预期结果的正确数据。我不知道 t-sql，但它的语法与 Oracle 的 sql 非常相似。我确信您可以采用下面的代码并将其转录为 t-sql。

    WITH       --  S a m p l e    D a t a :
    UserConversation (Id,   SenderId,   SenderDeleteDate,   RecipientId,    RecipientDeleteDate,    CreateDate) AS
        ( Select 1, 1002,   NULL,   1001,   NULL,   TIMESTAMP '2023-12-23 14:14:13.1152723 +07:00' From Dual Union All      
          Select 2, 1001,   NULL,   1003,   NULL,   TIMESTAMP '2023-12-23 14:15:20.1264302 +07:00' From Dual Union All
          Select 3, 1001,   NULL,   1004,   NULL,   TIMESTAMP '2023-12-23 14:16:57.4302621 +07:00' From Dual
        ),
    Users (Id,  UserName) AS
        ( Select 1001,  'JohnDoe' From Dual Union All
          Select 1002,  'BenDover' From Dual Union All
          Select 1003,  'JakeSmith' From Dual Union All
          Select 1004,  'KrisKringle' From Dual
        ),
    UserProfile (UserId,    Avatar) AS
        ( Select 1001,  'avatar/image/1001.jpg' From Dual Union All
          Select 1002,  'avatar/image/1002.jpg' From Dual Union All
          Select 1003,  'avatar/image/1003.jpg' From Dual Union All
          Select 1004,  'avatar/image/1004.jpg' From Dual 
        ),
    UserMessage (ConversationId,    SenderId,   RecipientId,    MessageRead,    Message,    CreateDate) AS
        ( Select 1, 1002,   1001,   0,  'Hello',    To_Date('2023-12-22 13:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 1, 1001,   1002,   0,  'Hi there', To_Date('2023-12-22 13:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1001,   1003,   1,  'Merry Christmas', To_Date('2023-12-22 14:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1003,   1001,   1,  'Same to you',  To_Date('2023-12-22 14:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1001,   1004,   1,  'Grab some beers?', To_Date('2023-12-22 15:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1004,   1001,   0,  'Sure', To_Date('2023-12-22 15:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual
        )

    甲骨文：

    --  M a i n    S Q L :
Select      c.ID "CONVERSATION_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.ID Else usnd.ID End) "USER_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.USERNAME Else usnd.USERNAME End) "USER_NAME", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then uprcp.AVATAR Else upsnd.AVATAR End) "USER_AVATAR",
            To_Char(Max(um.CREATEDATE), 'yyyy-mm-dd hh24:mi:ss') "LATEST_DATE",
            Count(Case When urcp.USERNAME = 'JohnDoe' And um.MessageRead = 0 Then 1 End) "UNREAD_COUNT"
From        UserMessage um
Inner Join  UserConversation c ON(um.ConversationId = c.ID)
Inner Join  Users usnd ON(usnd.ID = um.SENDERID)
Inner Join  Users urcp ON(urcp.ID = um.RECIPIENTID)
Inner Join  UserProfile upsnd ON(upsnd.USERID = c.SENDERID)
Inner Join  UserProfile uprcp ON(uprcp.USERID = c.RECIPIENTID)
Where       'JohnDoe' IN(usnd.USERNAME, urcp.USERNAME)
Group By    c.ID
Order By    c.ID Desc

    /*      R e s u l t :
CONVERSATION_ID    USER_ID USER_NAME   USER_AVATAR           LATEST_DATE         UNREAD_COUNT
--------------- ---------- ----------- --------------------- ------------------- ------------
              3       1004 KrisKringle avatar/image/1004.jpg 2023-12-22 15:30:00            1
              2       1003 JakeSmith   avatar/image/1003.jpg 2023-12-22 14:30:00            0
              1       1002 BenDover    avatar/image/1002.jpg 2023-12-22 13:30:00            1   */
    • 1

相关问题