SQL查询中的GROUP BY在与Order BY一起使用时出现语法错误

Question

我有这个查询：

SELECT customer.cus_id,customer.fname,customer.lname,customer.image, message.message ,message.msg_id,message.from_cus,message.to_cus, message.sent_time
FROM customer 
INNER JOIN message ON cus_id=message.from_cus OR cus_id=message.to_cus
WHERE customer.cus_id IN ( 31,26)
AND   (
from_cus =25
OR to_cus = 25
)
AND customer.cus_id NOT IN(25)
ORDER BY    message.sent_time DESC
GROUP BY customer.cus_id ;

其中31、26是我想要获取其最后消息的客户的cus_id。如果我删除GROUP BY约束，我将根据1和(31,26(客户之间的时间对所有消息进行排序。但添加它会给我这个语法错误

[Error code:1064 SQL state:42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY customer.cus_id' at line 11

我已禁用ONLY_FULL_GROUP_BY SQL模式。我仍然不知道如何获取最后一条信息。

Answer 1

这不是聚合操作，而是过滤操作。

使用窗口功能：

SELECT cm.* FROM (
SELECT customer.cus_id,customer.fname,customer.lname,customer.image, message.message ,message.msg_id,message.from_cus,message.to_cus, message.sent_time,
ROW_NUMBER() OVER (PARTITION BY customer.cus_id ORDER BY message.sent_time DESC) as seqnum
FROM customer 
INNER JOIN message ON cus_id=message.from_cus OR cus_id=message.to_cus
WHERE customer.cus_id IN ( 31,26)
AND   (
from_cus =25
OR to_cus = 25
)
AND customer.cus_id NOT IN(25)
) cm 
WHERE seqnum = 1
ORDER BY msg_id DESC;

Answer 2

Group by子句将位于order by子句之前

SELECT customer.cus_id,customer.fname,customer.lname,customer.image, message.message ,message.msg_id,message.from_cus,message.to_cus, message.sent_time
FROM customer 
INNER JOIN message ON cus_id=message.from_cus OR cus_id=message.to_cus
WHERE customer.cus_id IN ( 31,26)
AND   (
from_cus =25
OR to_cus = 25
)
AND customer.cus_id NOT IN(25)
GROUP BY customer.cus_id 
ORDER BY message.msg_id DESC