php - How to use 'ORDER BY' statement in a 'SELECT DISTINCT CASE WHEN' statement in sql & php?

Question

I am trying to create a chat application and am currently listing the conversations that the current user had.
This is my SQL Table: (Here 29 is the current user)

ID   |  FROM_USER  |  TO_USER  |  MESSAGE      
------------------------------------------------------------
1    |  16         |  29       |  Hey!         
2    |  29         |  18       | Hii..         
3    |  29         |  16       | What's up?
4    |  18         |  29       | Long time no see..

What I am trying to do is group the conversations in a descending order like:

18: Long time no see...
29 (to 16) : What's up?

The php code that I am currently using is as follow:

$query = mysqli_query($con,"SELECT DISTINCT CASE WHEN from_user = 29 THEN to_user ELSE from_user END as other_user FROM mytable WHERE from_user = 29 OR to_user = 29 ORDER BY id DESC");
while($fetch = mysqli_fetch_array($query)) {
$user = $fetch['other_user'];
echo $user.'<br/>';
}

But this doesn't seems to work. What am I doing wrong?
And thanks in advance!

标签： phpmysqlsqlchat

Answer 1

这是你的代码：

SELECT DISTINCT CASE WHEN from_user = 29 THEN to_user ELSE from_user END as other_user
FROM mytable
WHERE from_user = 29 OR to_user = 29
ORDER BY id DESC;

该列id未定义，因为它不在distinct. 我猜你不希望它在那里，因为它SELECT DISTINCT会返回重复other_user的 s。

我会推荐聚合而不是DISTINCT. MySQL 使这很容易，因为您可以在以下位置使用列别名GROUP BY：

SELECT (CASE WHEN from_user = 29 THEN to_user ELSE from_user END) as other_user
FROM mytable
WHERE 29 IN (from_user, to_user)
GROUP BY other_user
ORDER BY MAX(id) DESC;

这将按新近度顺序（或至少基于id列的新近度）返回其他用户。