很抱歉，没有使用准确的SQL

我使用这个查询来克服Mysql警告。

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

注意我存在的关键

count(*) AS cnt

这有助于我理解整个问题:

https://stackoverflow.com/a/20074634/1066234 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

下面是另一个问题查询的例子。

问题:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

通过在最后加上这个来解决:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

注意:查看PHP中的错误消息，它会告诉您问题出在哪里。

例子:

MySQL查询错误1140:在没有GROUP BY的聚合查询中，SELECT列表中的表达式#4包含非聚合列'db.gameplay.timestamp';这与sql_mode=only_full_group_by不兼容-查询:SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal，用户id，时间戳，questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM gameplay WHERE时间戳>= DATE_SUB(NOW()，间隔1天) AND userid = 1

在本例中，表达式#4在GROUP BY中缺失。

我不得不在我的Ubuntu 18.04上编辑下面的文件:

/ etc / mysql / mysql上。d / mysqld cnf。

with

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and

Sudo服务mysql重启

可以为group_id添加唯一索引;如果您确定group_id是唯一的。

它可以在不修改查询的情况下解决您的问题。

迟来的回答，但在回答中还没有提到。也许它应该完善现有的已经很全面的答案。至少，当我不得不分割一个包含太多字段的表时，它确实解决了我的问题。

我只需将group_id添加到GROUP BY。

When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT(), SUM(), MAX() etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it's best to get used to it and make your queries comply with it.

那么为什么我的答案如此简单呢?我做了几个假设:

1) group_id是唯一的。看起来很合理，毕竟这是一个“ID”。

2) group_name也是唯一的。这可能不是一个合理的假设。如果不是这种情况，您有一些重复的group_name，然后按照我的建议将group_id添加到GROUP BY，您可能会发现现在得到的结果比以前更多，因为具有相同名称的组现在将在结果中有单独的行。对我来说，这比隐藏这些重复的组要好，因为数据库已经悄悄地任意选择了一个值!

当涉及多个表时，使用表名或别名限定所有列也是一种很好的实践……

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name

我使用Laravel 5.3, mysql 5.7.12，在Laravel homestead(0.5.0，我相信)

即使在显式地设置编辑/etc/mysql/my.cnf以反映:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

我仍然在接收错误。

我不得不改变config/database.php从true到false:

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

进一步阅读:

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2