时间戳

首页 > 解决方案 > 当 sql_mode=only_full_group_by 时，在 mysql 中完成 Group By 工作的最佳方法是什么

php - 当 sql_mode=only_full_group_by 时，在 mysql 中完成 Group By 工作的最佳方法是什么

问题描述

我想执行一个查询，例如在我的本地主机上获取 DB 中任何类型的最后一条记录，我使用 Maria Db，查询如下：

SELECT * 
  FROM table_a
 WHERE column_a=999
    OR column_b=999 
 GROUP 
    BY `group`;

group是我在其中保存类型的列，例如：营销、博客、订单等

此查询在本地运行良好，但在服务器上我收到以下错误：

SQLSTATE[42000]: 
Syntax error or access violation: 
1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'db_name.table_a.id' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by\n
The SQL being executed was: 
SELECT * FROM `table_a` WHERE (`column_a`=999) OR (`column_b`=999) GROUP BY `group`"

根据MySQL 文档，我可以使用以下命令来实现这一点：

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

但我对 Db 没有足够的权限并收到以下错误：

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

我让主机帮我做这件事，他们回答说他们不想做这个动作

我使用 YII2 框架，现在我想要一种方法将其添加到框架的 database_config.php 选项中，或者将查询更改为具有相同结果的其他内容，并且不会损失性能

标签： phpmysqlsqlyii2greatest-n-per-group

解决方案

ONLY_FULL_GROUP_BY是一件好事，它强制执行基本的 ANSI SQL 规则。不要更改它，而是修复您的代码。

从那里开始：你想要整个记录，所以你不应该考虑聚合，而是过滤。

然后：在数据库表中，记录是无序的；为了使您的问题有意义，您需要一个定义每个组中行的顺序的列，因此“最后”记录的含义是明确的。让我假设您有这样的列，称为id.

以下是此 top-1-per-group 问题的典型方法，使用相关子查询进行过滤：

SELECT * 
FROM table_a a
WHERE 
    999 IN (column_a, column_b)
    AND id = (
        SELECT MAX(a1.id) 
        FROM table_a a1 
        WHERE 999 IN (a1.column_a, a1.column_b) AND a1.grp = a.grp
    )

或者，如果您正在运行 MySQL 8.0，则可以使用窗口函数：

SELECT *
FROM (
    SELECT a.*,
        ROW_NUMBER() OVER(PARTITION BY grp ORDER BY id DESC) rn
    FROM table_a a
    WHERE 999 IN (column_a, column_b)
) a
WHERE rn = 1

旁注：group是一个语言关键字，因此对于列名来说是一个糟糕的选择。我在查询中将其重命名为grp。

推荐阅读