php - mysql 的 sql_mode问题～～ONLY_FULL_GROUP_BY

问题描述

问 题

5.7版的mysql这对于之前版本的group by总是不能用！
有没有较妥当的解决办法？更改sql_mode好像会出现性能问题吧？重写sql怎么写？

SELECT
                    s.id,
                    s.users_id,
                    COUNT(*) as attempt,
                    s.time
                    s.language,
                    s.is_public,
                    u.name,
                    u.nick
                FROM
                    s
                INNER JOIN users u ON u.id = s.users_id
                WHERE s.sid = 10 AND s.result = 1
                GROUP BY s.users_id
                ORDER BY s.time

解决方案

SELECT
    s.id,
    s.users_id,
    COUNT(*) as attempt,
    s.time,
    s.language,
    s.is_public,
    u.name,
    u.nick
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.users_id,s.id,s.time,s.language,s.is_public,u.name,u.nick
ORDER BY s.time

或者

SELECT
    s.users_id,
    COUNT(*) as attempt,
FROM
    s
INNER JOIN users u ON u.id = s.users_id
WHERE s.sid = 10 AND s.result = 1
GROUP BY s.users_id
ORDER BY s.time

因为假如s表有两行数据，他们的users_id相等，但是id不相等，按照你的写法，mysql不知道s.id该输出什么

这篇关于php - mysql 的 sql_mode问题～～ONLY_FULL_GROUP_BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！