获取最后一行PER
我怎样才能制定以下任务查询：获取最后一行PER
比方说，你登录为用户：1 我想每谈话我已经拿到一行。 对于每一行我想要得到的，谈话 第一行的“日期时间” “消息”这次谈话不管是谁写的
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
嗨马丁这很好！ 只是情侣，我得到的消息不是来自对话的最新消息，我实际上真正在寻找的DateTime是其他用户在此对话中发送的最后一条消息的时间。 – user391986 2010-08-08 21:14:44
我认为这个问题可能是您的测试表中的NOW（），这意味着所有的日期时间是相同的？如果是这样，可以使用id。 – 2010-08-08 21:17:28
就是这样！这几乎是完美的，我用新的数据编辑了这篇文章。最后一件事我怎么也可以得到与我交谈的用户的ID还为每一行（对话） – user391986 2010-08-08 21:37:02
你应该尝试这样的事：
SELECT
m1.Subject,
m1.DateTime,
m2.Message
FROM conversations c
INNER JOIN
(SELECT MIN(ID) AS minID,
MAX(ID) AS maxID,
ConversationID
FROM messages
WHERE FromID = @userID OR ToID = @userID
GROUP BY ConversationID) AS cGrouped
ON c.ConversationID = cGrouped.ConversationID
INNER JOIN messages m1 ON m1.ID = cGrouped.minID
INNER JOIN messages m2 ON m2.ID = cGrouped.maxID
只介于2人的谈话？即用户ID是否可以是对话中所有消息的'FromID'或'ToID'？ – 2010-08-08 20:49:11
是的对话只有两个人之间 – user391986 2010-08-08 21:12:53