简单！在线急等求查一条SQL语句，获取聊天列表

[jiangnan601]

表内容：


要求结果：
查出和吕布对话过的最后一条信息列表，按时间倒序，不论发送者是吕布还是接受者是吕布。

结果要求：


40 赵云 2222 2019-08-23 18:59:00.000
35 曹操 1 2019-08-22 23:56:00.000
38 孙权 545 2019-08-22 18:55:00.000
37 刘备 33 2019-08-22 10:00:00.000

[二月十六]

SELECT t.OpenId,
                  MAX(t.CreateDate) AS CreateDate
             FROM (   SELECT *,
                             CASE
                                  WHEN Opendid1 = '吕布' THEN Opendid2
                                  ELSE Opendid1 END AS OpenId
                        FROM Chat
                       WHERE Opendid1 = '吕布'
                          OR Opendid2 = '吕布') t
            GROUP BY OpenId

[jiangnan601]

引用 1 楼 二月十六 的回复:

SELECT t.OpenId,
                  MAX(t.CreateDate) AS CreateDate
             FROM (   SELECT *,
                             CASE
                                  WHEN Opendid1 = '吕布' THEN Opendid2
                                  ELSE Opendid1 END AS OpenId
                        FROM Chat
                       WHERE Opendid1 = '吕布'
                          OR Opendid2 = '吕布') t
            GROUP BY OpenId

内容没出来吧，要显示最后一条的内容哦

[二月十六]

引用 2 楼 jiangnan601 的回复:

[quote=引用 1 楼 二月十六 的回复:]

SELECT t.OpenId,
                  MAX(t.CreateDate) AS CreateDate
             FROM (   SELECT *,
                             CASE
                                  WHEN Opendid1 = '吕布' THEN Opendid2
                                  ELSE Opendid1 END AS OpenId
                        FROM Chat
                       WHERE Opendid1 = '吕布'
                          OR Opendid2 = '吕布') t
            GROUP BY OpenId

内容没出来吧，要显示最后一条的内容哦[/quote] 最后和吕布说话的人和时间出来了，再关联查询一下就行了。要不你给点测试数据，别贴图了，我写一下

[jiangnan601]

引用 3 楼 二月十六 的回复:

[quote=引用 2 楼 jiangnan601 的回复:]
[quote=引用 1 楼 二月十六 的回复:]

SELECT t.OpenId,
                  MAX(t.CreateDate) AS CreateDate
             FROM (   SELECT *,
                             CASE
                                  WHEN Opendid1 = '吕布' THEN Opendid2
                                  ELSE Opendid1 END AS OpenId
                        FROM Chat
                       WHERE Opendid1 = '吕布'
                          OR Opendid2 = '吕布') t
            GROUP BY OpenId

内容没出来吧，要显示最后一条的内容哦[/quote]
最后和吕布说话的人和时间出来了，再关联查询一下就行了。要不你给点测试数据，别贴图了，我写一下[/quote]


USE [TQDB]
GO
/****** Object: Table [dbo].[Chat] Script Date: 08/22/2019 09:18:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Chat](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Openid1] [nvarchar](50) NULL,
[Openid2] [nvarchar](50) NULL,
[ContentMsg] [nvarchar](max) NULL,
[ContentImages] [nvarchar](200) NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK_Chat] 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]
GO
SET IDENTITY_INSERT [dbo].[Chat] ON
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (35, N'吕布', N'曹操', N'1', NULL, CAST(0x0000AAB1018A68C0 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (36, N'吕布', N'曹操', N'22', NULL, CAST(0x0000AAB100888150 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (37, N'吕布', N'刘备', N'33', NULL, CAST(0x0000AAB100A4CB80 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (38, N'吕布', N'孙权', N'545', NULL, CAST(0x0000AAB10137BCB0 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (39, N'赵云', N'吕布', N'545', NULL, CAST(0x0000AAB10138D5F0 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (40, N'赵云', N'吕布', N'2222', NULL, CAST(0x0000AAB20138D5F0 AS DateTime))
INSERT [dbo].[Chat] ([ID], [Openid1], [Openid2], [ContentMsg], [ContentImages], [CreateDate]) VALUES (41, N'曹操', N'吕布', N'2222', NULL, CAST(0x0000AAB20138D5F0 AS DateTime))
SET IDENTITY_INSERT [dbo].[Chat] OFF

[二月十六]

;WITH t AS (
SELECT *,
                             CASE
                                  WHEN Openid1 = '吕布' THEN Openid2
                                  ELSE Openid1 END AS OpenId
                        FROM Chat
                       WHERE Openid1 = '吕布'
                          OR Openid2 = '吕布'
),t1 AS (
SELECT t.OpenId,
       MAX(t.CreateDate) AS CreateDate
  FROM t
 GROUP BY OpenId
)
SELECT t.ID,t.OpenId,t.ContentMsg,t.CreateDate FROM t JOIN t1 ON t1.OpenId = t.OpenId AND t1.CreateDate = t.CreateDate

[xs共享精灵]

select ID,case when Openid1='吕布' then Openid2 else Openid1 end Openid,ContentMsg,CreateDate from ( select ROW_NUMBER() over(partition by Openid1,Openid2 order by createdate)x, * from Chat where Openid1='吕布' or Openid2='吕布' ) r where r.x=1