mysql group by 报错 1055

最新推荐文章于 2025-11-29 15:49:05 发布

原创最新推荐文章于 2025-11-29 15:49:05 发布 · 245 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #group by

mysql 专栏收录该内容

7 篇文章

订阅专栏

在MySQL 5.7.31中遇到一个错误，关于SQL查询不兼容`only_full_group_by`模式。原始查询在GROUP BY子句中缺少聚合函数，导致问题。通过将每个非聚合列用MAX函数包裹并更新GROUP BY子句，成功解决了问题。修复后的查询能够正确地按ward_name分组并提供所需统计数据。

http://www.hellojava.com/a/54977.html

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘qdhr_dwd_test.dwd_user_department.company_id’ which is not >functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql :5.7.31

mysql语句如下:

SELECT 
    pu.pu_ward_name, 
    entry_date,
    pu.o_code,
    pu.pu_ward_name,
    pu.pu_lga_name,
    pu.pu_code,
    pu.pu_ward_code,
    pu.pu_lga_code,
    pu.pu_voters_reg_count, 
    SUM(total_vote_cast) AS total_vote_cast,
    SUM(apc_total_vote) AS apc_total_vote,
    SUM(pdp_total_vote) AS pdp_total_vote,
    SUM(adp) AS adp, ward_name
FROM 
    post_election_info r 
    LEFT JOIN polling_unit pu ON r.ward_name = pu.pu_ward_name 
WHERE lga_name = 'BORIPE'
GROUP BY 
    pu.pu_ward_name

修改:

SELECT 
    pu.pu_ward_name, 
    MAX(entry_date)             AS entry_date,
    MAX(pu.o_code)              AS o_code,
    MAX(pu.pu_lga_name)         AS pu_lga_name,
    MAX(pu.pu_code)             AS pu_code,
    MAX(pu.pu_ward_code)        AS pu_ward_code,
    MAX(pu.pu_lga_code)         AS pu_lga_code,
    MAX(pu.pu_voters_reg_count) AS pu_voters_reg_count, 
    SUM(total_vote_cast)        AS total_vote_cast,
    SUM(apc_total_vote)         AS apc_total_vote,
    SUM(pdp_total_vote)         AS pdp_total_vote,
    SUM(adp)                    AS adp
FROM 
    post_election_info r 
    LEFT JOIN polling_unit pu ON r.ward_name = pu.pu_ward_name 
WHERE lga_name = 'BORIPE'
GROUP BY 
    pu.pu_ward_name