MYSQL distincs entry of two columns even when swit

问题:

my mysql table has the following structure:

ID    Sender    Recipient    Text
--    ------    ---------    ----
 1    10        11           Text
 2    10        11           Text
 3    11        10           Text
 4    11        10           Text
 5    12        13           Text
 6    13        12           Text
 7    14        15           Text
 8    14        15           Text
 9    17        16           Text
10    16        17           Text

This table represents a private message conversation between two users. I now would like a list were i show each chat only once. Normally i would go with a group by in this case but then i still have a problem because the sender and recipient column can also be the other way around.

I would like to recieve a result like that:

ID    Sender    Recipient    Text
--    ------    ---------    ----
 1    10        11           Text
 2    12        13           Text
 3    14        15           Text
 4    16        17           Text

So each of the conversations would be unique. Does anyone have an idea how to solve this?

回答1:

You could use a query like this:

select
  min(id) as id,
  least(sender,recipient) as sender,
  greatest(sender,recipient) as recipient
  min(text) as text
from
  tablename
group by
  least(sender,recipient),
  greatest(sender,recipient)

(or you can use another aggregated function for the text, like group_concat, or you have to join tablename twice to get the first or the last msg of the chat, but this depends on what you want to return)

回答2:

With your sample schema:

CREATE TABLE t (id int NOT NULL AUTO_INCREMENT, Sender int, Recipient int, Text varchar(50), primary key (id));
INSERT INTO t (Sender, Recipient, Text) VALUES (10, 11, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (10, 11, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (11, 10, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (11, 10, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (12, 13, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (13, 12, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (14, 15, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (14, 15, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (17, 16, 'Text');
INSERT INTO t (Sender, Recipient, Text) VALUES (16, 17, 'Text');

You can write this query:

SELECT id, Sender, Recipient, Text
FROM t
JOIN (
        SELECT MAX(id) as last_id,
                CASE WHEN Sender < Recipient THEN CONCAT_WS('-', Sender, Recipient)
                     ELSE CONCAT_WS('-', Recipient, Sender)
                END as conversation
        FROM t
        GROUP BY conversation
    ) as t2 ON t2.last_id = t.id
;

The output is:

+------+----------+-------------+--------+
|   id |   Sender |   Recipient | Text   |
|------+----------+-------------+--------|
|    4 |       11 |          10 | Text   |
|    6 |       13 |          12 | Text   |
|    8 |       14 |          15 | Text   |
|   10 |       16 |          17 | Text   |
+------+----------+-------------+--------+
4 rows in set