27,579
社区成员
发帖
与我相关
我的任务
分享
WITH Messages (ID,ToEmpID,FromEmpID,SendDate,Detail) AS
(
SELECT 1,'张三','李四','2014/06/05','a' UNION ALL
SELECT 2,'王五','张三','2014/06/03','b' UNION ALL
SELECT 3,'刘六','张三','2014/06/02','b' UNION ALL
SELECT 4,'张三','李四','2014/05/05','a' UNION ALL
SELECT 5,'王五','张三','2014/05/03','b' UNION ALL
SELECT 6,'刘六','张三','2014/05/02','b'
)
SELECT ID,ToEmpID,FromEmpID,SendDate FROM Messages a
WHERE ID in
(
SELECT TOP 1 ID
FROM Messages
where ToEmpID=a.ToEmpID AND FromEmpID=a.FromEmpID
ORDER BY SendDate DESC
)
DECLARE @Message TABLE(ID INT,ToEmpID NVARCHAR(200),FromEmpID NVARCHAR(200),SendDate DATETIME,Detail NVARCHAR(500))
INSERT INTO @Message(ID,ToEmpID,FromEmpID,SendDate,Detail)
SELECT 1,'张三','李四','2014/06/05','ha ha' UNION ALL
SELECT 2,'李四','张三','2014/06/06','he he' UNION ALL
SELECT 3,'张三','李四','2014/06/07','bye bye' UNION ALL
SELECT 4,'张三','王五','2014/05/05','SB' UNION ALL
SELECT 5,'王五','张三','2014/05/07','NB' UNION ALL
SELECT 6,'刘六','张三','2014/05/02','ML' UNION ALL
SELECT 7,'张三','刘六','2014/06/07','DL'
SELECT FromEmpID AS 发件人
,ToEmpID AS 收件人
,SendDate AS 最新日期
FROM @Message a
WHERE NOT EXISTS(SELECT * FROM @Message
WHERE ((a.ToEmpID = ToEmpID AND a.FromEmpID = FromEmpID)
OR (a.ToEmpID = FromEmpID AND a.FromEmpID = ToEmpID))
AND SendDate > a.SendDate)
发件人 收件人 最新日期
----------------------------- --------------------------------------------------------
李四 张三 2014-06-07 00:00:00.000
张三 王五 2014-05-07 00:00:00.000
刘六 张三 2014-06-07 00:00:00.000
(3 行受影响)