SQL将两列分组，然后选择最近的日期/时间戳

Question

所以我有一个名为phone_messages的表。这是我编写的一个示例查询。我要做的是将具有character_id 22或target_character_id 22的行分组在一起。因为在数据库中存储的每个phone_message中都有两个条目。基本上是电话交谈。我想做的是在每个群组中发送最后一条消息。

我的原始查询：

SET @character_id = 22;
SET @t = 'text';
SELECT character_id, target_character_id, message, `type`, MAX(`date`) date FROM
`phone_messages`
WHERE
(
`character_id`=@character_id
) AND `type`=@t
GROUP BY
character_id, target_character_id

---

character_id | target_character_id | message | type | date
"22"    "33"    "correct"   "text"  "2020-08-25 23:28:31"
"33"    "22"    "perfect see you then"  "text"  "2020-08-25 23:28:43"
"57"    "22"    "where is this mega mall" "text" "2020-09-05 19:05:25"
"22"    "57"    "the tool shop down south"  "text"  "2020-09-05 19:05:45"

---

我想要的输出是

character_id | target_character_id | message | type | date
"33"    "22"    "perfect see you then"  "text"  "2020-08-25 23:28:43"
"22"    "57"    "the tool shop down south"  "text"  "2020-09-05 19:05:45"

---

我该怎么做？很抱歉对我的问题缺乏理解。

Answer 1

仅用于确认

• 对于每个"对"呼叫者(其中一个是"22"(
• 你想获得最新的文本，无论是哪种方式(到或从22(

这里的答案使用以下方法

• 对于每一行，确定另一方(在查询中称为second_character_id(
• 对于其他各方，根据发送日期对"文本"行进行排序
• 获取每个聚会的最新一排

WITH Call_list AS
(SELECT *,
@character_id AS `primary_character_id`,
CASE 
WHEN `character_id` = @character_id THEN `target_character_id`
ELSE `character_id` END AS `second_character_id`
FROM
`phone_messages`
WHERE
(`character_id`= @character_id OR `target_character_id`= @character_id)
AND (`type`= @t )
),
Sorted_Call_List AS
(SELECT *,
ROW_NUMBER() OVER 
(PARTITION BY `primary_character_id`, `second_character_id` 
ORDER BY `Date` DESC
) AS char_rn
FROM Call_list
)
SELECT `character_id`, `target_character_id`, `message`, `type`, `date`
FROM   Sorted_Call_List
WHERE  char_rn = 1;

在这个答案中，我还包括了primary_character_id——在这种情况下，它总是22——以防您想将其扩展为多人。

这里有一个db<gt；篡改数据设置和方法。

请注意，底部还有一个额外的查询——那是我之前误解需求的尝试。在这个答案中，它会找到发送到/来自22的最新"传入"和最新"传出"文本，无论它们来自谁。

Answer 2

如果您想要每对的最新消息，无论排序如何，那么您可以将ROW_NUMBER()与LEAST()和GREATEST():一起使用

SET @character_id = 22;
SET @t = 'text';
SELECT pm.*
FROM (SELECT pm.*,
ROW_NUMBER() OVER (PARTITION BY LEAST(character_id, target_character_id), GREATEST(character_id, target_character_id)
ORDER BY date DESC
) as seqnum
FROM phone_messages pm
WHERE character_id = @character_id AND
type = @t
) pm
WHERE seqnum = 1;