获取PER组的最后一行 [英] Get last row PER Group

问题描述

如何为以下任务制定查询:

How can I formulate a query for the below task:

假设您以以下用户身份登录:1 我想在每次对话中排成一行. 对于我想获得的每一行， 对话中第一行的主题" 第一行的"DateTime" 对话的消息"最后一条消息，无论是谁写的

Let's say you are logged in as user:1 I want to get one row per conversations I've had. For each row I want to get, the "Subject" of the first row within the conversation "DateTime" of the first row "Message" last message of this conversation no matter who wrote it

CREATE TABLE messages (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FromID INT NOT NULL,   
    ToID INT NOT NULL,    
    ConversationID INT NOT NULL,    
    Subject varchar(255), 
    Message varchar(255),
    DateTime DATETIME                                       
    ) ENGINE=InnoDB;


CREATE TABLE conversations (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY                                    
    ) ENGINE=InnoDB;



INSERT INTO conversations (ID) VALUES (1), (2), (3);
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 1, "Hi", "This is a test message", "2010-08-08 16:23:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 1, "", "Hey again you have not answered", "2010-08-08 16:23:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 1, "", "Hi this is my answer", "2010-08-08 16:23:59");


INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 2, "2.Hi", "2.This is a test message", "2010-08-08 16:25:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 2, "", "2.Hi back", "2010-08-08 16:25:52");                                                                                                                                               
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 2, "", "2.Hi this is my answer", "2010-08-08 16:25:59");


INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (2,1, 3, "3.Hi", "3.This is a test message", "2010-08-08 16:27:48");        
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 3, "", "2.Hi back", "2010-08-08 16:27:52");   
INSERT INTO messages (FromID, ToID, ConversationID, Subject, Message, DateTime) VALUES (1,2, 3, "", "2.Hello are you there?", "2010-08-08 16:27:59");                                                                                                                                            

推荐答案

SELECT M.ConversationID, 
MAX(CASE WHEN M.DateTime = X.FirstRow THEN M.Subject END) AS Subject,
CAST(COALESCE(MAX(CASE WHEN M.DateTime = X.LastRowSentByOtherUser 
                       THEN M.DateTime END),X.LastRow) AS DateTime)AS LastTime,
MAX(CASE WHEN M.DateTime = X.LastRow THEN M.Message END) AS Message,
MAX(CASE WHEN FromID = 1 THEN ToID ELSE FromID END) AS OtherParticipantId
FROM messages M
JOIN (
    SELECT ConversationID, MIN(DateTime) AS FirstRow, MAX(DateTime) AS LastRow,
    MAX(CASE WHEN FromID<>1 THEN DateTime END) AS LastRowSentByOtherUser
    FROM messages
    WHERE FromID=1 OR ToID=1
    GROUP BY ConversationID
) X ON X.ConversationID = M.ConversationID
AND (M.DateTime IN (X.FirstRow, X.LastRow, X.LastRowSentByOtherUser))
GROUP BY M.ConversationID
HAVING MAX(CASE WHEN M.DateTime = X.LastRowSentByOtherUser 
                   THEN M.DateTime END) IS NOT NULL

这篇关于获取PER组的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！