>我有一个消息表,其中包含如下列
message_id、message_to_id、message_from_id、message_body project_id。
我想要特定用户的消息线程列表。 消息线程应具有字段的唯一组合 message_to_id、message_from_id 和 project_id。
早些时候我在project_id上使用分组,但我不想让他们在项目上发送分组消息。
SELECT * FROM message
WHERE message_to = '{$user_id}' OR message_from = '{$user_id}'
GROUP BY project
这没有按预期工作。
SELECT *
FROM message
WHERE message_to = '{$user_id}'
GROUP BY message_from
UNION
SELECT *
FROM message
WHERE message_from = '{$user_id}'
GROUP BY message_to
这向我展示了我不想要的message_from和message_to的组合。
我想要单个项目上每个用户组合的消息线程。
示例数据
message_id | message_from_id | message_to_id | project_id | message _body
1 | 283 | 284 | 4 | Hello
2 | 284 | 283 | 4 | Hi
3 | 285 | 283 | 4 | Hey there!
4 | 283 | 285 | 4 | Greetings
5 | 283 | 284 | 6 | Cool!
我想要什么
message_id | message_from_id | message_to_id | project_id | message _body
1 | 283 | 284 | 4 | Hello
3 | 285 | 283 | 4 | Hey there!
5 | 283 | 284 | 6 | Cool!
即忽略message_from_id并message_to_id排序,但与project_id一起它们应该是唯一的。
我使用ON
或排名方法(ROW_NUMBER
或DENSE_RANK
(提出了一个完全不同的解决方案,尽管这并不兼容所有SQL版本,环顾四周,我发现了一种似乎可以完成这项工作的JOIN
方法。
这是我的结果:
SELECT message.message_id, message_to_id, message.message_from_id, message.project_id, message.message_body FROM
message
INNER JOIN
(
SELECT MIN(message_id) message_idMIN FROM message WHERE message_to_id = 283 GROUP BY project_id, message_to_id
UNION
SELECT MIN(message_id) message_idMIN FROM message WHERE message_from_id = 283 GROUP BY project_id, message_from_id
) message_group
ON message.message_id = message_group.message_idMIN;
请看这里的小提琴:http://sqlfiddle.com/#!9/ef36a1/32
正确答案是忽略message_to和message_from的组合,并给出相同的区别。
SELECT DISTINCT
CASE WHEN message_from_id < message_to_id
THEN message_from_id
ELSE message_to_id END as message_from_id,
CASE WHEN message_from_id < message_to_id
THEN message_to_id
ELSE message_from_id END as message_to_id,
project
FROM message
WHERE message_to_id = '{$user_id}' OR message_from_id = '{$user_id}'
这样,它可以在没有分组依据子句的情况下工作。
但是这个解决方案的问题是我无法获得任何像sent_at这样的非不同列(我在这里没有提到(。