Mysql语句从两列中选择不同的行 [英] Mysql statement to select distinct rows from two columns

问题描述

我正在尝试编写一个只从我的聊天系统中提取唯一消息的mysql语句。
有一个to_id和一个from_id。我想从每个对话中拉一行。

问题是如果有一行含有
to_id = 1
from_id = 2
，另一行含有
to_id = 2
from_id = 1
我希望将这两行视为相同。

我提出的声明是 SELECT * FROM chat_messages GROUP BY to_id，from_id。除了上面提到的情况之外，这是有效的。

我做的另一个尝试是使用SELECT DISTINCT least（to_id，from_id）作为value1，最大（to_id，from_id）作为来自chat_messages的值2。 b
$ b

这将返回我需要的内容，但我不知道如何从行中获取剩余信息，如消息，时间戳记等。它只给我value1& value2，它们是to_id& from_id。

解决方案

只需在第一个查询的GROUP BY的第二个查询中使用表达式：

  SELECT * FROM chat_messages GROUP BY LEAST（to_id，from_id），GREATEST（to_id，from_id）
  pre> 
I'm trying to write a mysql statement that pulls only unique messages from my chat system.
There is a to_id and a from_id. I want to pull one row from every conversation. 

The problem is if there is a row with
    to_id = 1 
    from_id = 2 
and another row with 
    to_id = 2
    from_id = 1
I want both of these rows to be treated as the same.

The statement I came up with is "SELECT * FROM chat_messages GROUP BY to_id,from_id ". This works except for the situation I mentioned above. 

Another attempt I made was using "SELECT DISTINCT least(to_id,from_id) as value1, greatest(to_id,from_id) as value2 from chat_messages ".

This returns what I need but I do not know how to get the rest of the information from the row like the message, timestamp etc. It only gives me value1 & value2 which are the to_id & from_id.
解决方案
Just use the expressions in the second query for the GROUP BY of the first query:
SELECT * FROM chat_messages GROUP BY LEAST(to_id,from_id), GREATEST(to_id,from_id)


                        
这篇关于Mysql语句从两列中选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！