mysql - "sql_mode=only_full_group_by"不兼容错误

Question

我的 SQL 语句在另一台本地服务器上运行良好，但在最近安装的 lamp 服务器上尝试我的项目后，它显示以下错误-

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'newmesssages.send_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

声明-

SELECT COUNT(reciever) as total, send_date, message, sender_username, sender_name 
FROM (SELECT * FROM messages_view WHERE reciever = 17 AND viewed_by_reciever is NULL order by send_date DESC) 
AS newmesssages 
GROUP BY sender ORDER BY send_date DESC LIMIT 20

Answer 1

您需要按所有不聚合的列进行分组，如下所示:

SELECT COUNT(reciever) as total, send_date, message, sender_username, sender_name 
FROM (SELECT * 
     FROM messages_view 
     WHERE reciever = 17 AND viewed_by_reciever is NULL order by send_date DESC) 
     AS newmesssages 
GROUP BY send_date, message, sender_username, sender_name 
ORDER BY send_date DESC LIMIT 20

如果您不想按这些列分组，则需要将它们从 SELECT 中删除。

另一个解决方案是改变 sql_mode 这样做:

SET SESSION sql_mode = REPLACE(@@sql_mode, ',only_full_group_by', '');

最后，你的 SQL 出错了，你在按 sender 分组但你没有那个列名

已编辑:你可以这样做:

SELECT (SELECT COUNT(reciever)  
        FROM (SELECT * 
              FROM messages_view 
              WHERE reciever = 17 AND viewed_by_reciever is NULL 
              order by send_date DESC) AS newmesssages 
        GROUP BY send_date, message, sender_username, sender_name),
        send_date, message, sender_username, sender_name 
FROM (SELECT * 
     FROM messages_view 
     WHERE reciever = 17 AND viewed_by_reciever is NULL order by send_date DESC) 
ORDER BY send_date DESC LIMIT 20

我认为这可行