sql - 聊天记录表如何查询最新数据？

问题描述

问 题

表结构和测试数据如下：

USE chifanos
GO
/****** 对象:  Table [dbo].[Messlist]    脚本日期: 12/22/2016 09:19:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Messlist](
    [Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [MyId] [int] NOT NULL CONSTRAINT [DF_Messlist_MyId]  DEFAULT ((0)),
    [ToId] [int] NOT NULL CONSTRAINT [DF_Messlist_ToId]  DEFAULT ((0)),
    [Contents] [ntext] NOT NULL,
    [AddTime] [datetime] NOT NULL CONSTRAINT [DF_Messlist_AddTime]  DEFAULT (getdate()),
    [IsRead] [int] NOT NULL CONSTRAINT [DF_Messlist_IsRead]  DEFAULT ((0)),
    [IsDel] [int] NOT NULL CONSTRAINT [DF_Messlist_IsDel]  DEFAULT ((0)),
 CONSTRAINT [PK_Messlist] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [chifanos].[dbo].[Messlist]([MyId],[ToId],[Contents],[AddTime],[IsRead],[IsDel])
     VALUES (50,120,'我是内容内容1',GETDATE(),0,0)
INSERT INTO [chifanos].[dbo].[Messlist]([MyId],[ToId],[Contents],[AddTime],[IsRead],[IsDel])
     VALUES (120,50,'我是内容内容2',GETDATE(),0,0)
INSERT INTO [chifanos].[dbo].[Messlist]([MyId],[ToId],[Contents],[AddTime],[IsRead],[IsDel])
     VALUES (50,23,'我是内容内容3',GETDATE(),0,0)
INSERT INTO [chifanos].[dbo].[Messlist]([MyId],[ToId],[Contents],[AddTime],[IsRead],[IsDel])
     VALUES (50,28,'我是内容内容4',GETDATE(),0,0)
INSERT INTO [chifanos].[dbo].[Messlist]([MyId],[ToId],[Contents],[AddTime],[IsRead],[IsDel])
     VALUES (35,50,'我是内容内容4',GETDATE(),0,0)

字段说明：MyId(发送者ID) ToId(接受者ID) Contents(发送内容) AddTime(发送时间)

没学好SQL，怎么查询和自己聊天所有用户最新的消息。

如上测试数据，则需求结果如下：

ToId .....
120
23
28
35

解决方案

具体 SQLserver 语法可能有不同我按照自己的理解写一个

SELECT distinct a.Id FROM Messlist a WHERE a.ToId=50 UNION SELECT  distinct b.ToId FROM Messlist b WHERE b.Id=50;

这篇关于sql - 聊天记录表如何查询最新数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！