如何从显示中排除几行？ "DISTINCT" - SQL 服务器

Question

，所以我试图仅从某人中选择最后一条消息。这是我想到的查询

SELECT DISTINCT ISNULL(MessageID, -1) AS "MessageID", ContactID, Msgs.DateS
FROM Contacts LEFT JOIN Msgs ON Contacts.ContactID IN (Msgs.SenderID, Msgs.RecieverID)
WHERE AccountID = 1 ORDER BY Msgs.DateS ASC;

但它仍然向我展示其他消息，我只想出现一条消息。这是结果表：

图片显示结果表

Answer 1

SELECT DISTINCT ISNULL(m.MessageID, -1) AS "MessageID", ContactID, m.DateS
FROM Contacts c
    OUTER APPLY (
        SELECT TOP 1  Msgs.MessageID, Msgs.DateS 
        FROM Msgs 
        WHERE c.ContactID IN (SenderID, RecieverID)
        ORDER BY DateS DESC
    ) m
WHERE AccountID = 1;

Answer 2

用于SQL Server 2005或更高版本

SELECT 
    TOP 1 WITH TIES ISNULL(MessageID, -1) AS "MessageID", ContactID, Msgs.DateS
FROM 
    Contacts 
    LEFT JOIN Msgs ON Contacts.ContactID IN (Msgs.SenderID, Msgs.RecieverID)
WHERE 
    AccountID = 1 
ORDER BY ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY Msgs.DateS DESC)

Answer 3

而不是DISTINCT，您需要aggregate function

SELECT ISNULL(MessageID, -1) AS "MessageID", ContactID, MAX(Msgs.DateS)
FROM Contacts 
LEFT JOIN Msgs ON Contacts.ContactID IN (Msgs.SenderID, Msgs.RecieverID) 
WHERE AccountID = 1 
GROUP BY MessageID, ContactID
ORDER BY Msgs.DateS ASC;

Answer 4

如果仅想要"最后"消息，请使用 top 1：

SELECT TOP 1 COALESCE(m.MessageID, -1) AS "MessageID", c.ContactID, m.DateS
FROM Contacts c LEFT JOIN
     Msgs m
     ON c.ContactID IN (m.SenderID, m.RecieverID)
WHERE AccountID = 1
ORDER BY Msgs.DateS DESC;

Answer 5

只是一个想法，请尝试以下操作：

SELECT ISNULL(MessageID, -1) AS "MessageID", ContactID, max(m.DateS)
FROM Contacts c
LEFT JOIN Msgs m ON c.ContactID IN (m.SenderID, m.RecieverID)
WHERE AccountID = 1 Group By MessageID,c.ContactID 
ORDER BY m.DateS ASC;

Answer 6

SELECT * FROM (
        SELECT  ISNULL(MessageID, -1) AS "MessageID", ContactID, Msgs.DateS,ROW_NUMBER()OVER(PARTITION BY AccountID ORDER BY Msgs.DateS DESC ) AS Seq
        FROM Contacts 
        LEFT JOIN Msgs ON Contacts.ContactID IN (Msgs.SenderID, Msgs.RecieverID)
) AS t WHERE t.seq=1 AND AccountID = 1 
ORDER BY Msgs.DateS ASC;