SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘guest.id’ which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by|
SELECT count(*) as mcount,store_id FROM guest WHERE ( sex=1 and store_id in (123,2,1) ) GROUP BY store_id ORDER BY id LIMIT 0,3
原因：ORDER BY id 中的 id 字段没有出现在 GROUP BY 子句中且不是聚合函数。
方案1：将 ORDER BY 子句改为使用聚合函数或者确保 ORDER BY 中的字段在 GROUP BY 子句中：
SELECT COUNT(*) AS mcount, `store_id`
FROM `guest `
WHERE sex=1 AND store_id IN (123, 2, 1)
GROUP BY `store_id`
ORDER BY mcount
LIMIT 0, 3;
方案2：通过子查询来解决这个问题，可以先进行分组再排序：
SELECT * FROM (
SELECT COUNT(*) AS mcount, `store_id`
FROM `guest`
WHERE sex=1 AND store_id IN (123, 2, 1)
GROUP BY `store_id`
) AS grouped_table
ORDER BY mcount
LIMIT 0, 3;