mysql - 为什么我不能在 MySQL 的 GROUP BY 查询中使用多列？

Question

我需要创建一个按三个不同列分组的 SQL，这是我的查询:

SELECT 
    notification.type, 
    notification.triggerer, 
    notification.to_what,
    ANY_VALUE(user_data.name) AS name,
    ANY_VALUE(user_data.surname) AS surname,
    ANY_VALUE(user_data.avatarimage) AS avatarimage,
    COUNT(*) AS counter
FROM notification 
INNER JOIN user_data
    ON notification.triggerer = user_data.owner
WHERE notification.owner = "c6cecc891f6c4cc84cc0b62062578e52"
    AND isdelete=0
ORDER BY notification.id DESC
GROUP BY notification.triggerer, notification.type, notification.to_what
LIMIT 0,100

但是当我这样做时，它显示了一个错误:

> #1064 - 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 notification.triggerer, notification.type, notification.to_what
LIMIT ' at line 15

如何修复它？

Answer 1

你可以使用它，但你的group by必须在order by之前

SELECT 
    notification.type, 
    notification.triggerer, 
    notification.to_what,
    ANY_VALUE(user_data.name) AS name,
    ANY_VALUE(user_data.surname) AS surname,
    ANY_VALUE(user_data.avatarimage) AS avatarimage,
    COUNT(*) AS counter
FROM notification 
INNER JOIN user_data
    ON notification.triggerer = user_data.owner
WHERE notification.owner = "c6cecc891f6c4cc84cc0b62062578e52"
    AND isdelete=0
GROUP BY notification.triggerer, notification.type, notification.to_what
ORDER BY notification.id DESC
LIMIT 0,100