(SELECT a.*, i.bank_card, i.open_bank, u.id_card FROM `payment_contribution_detail` a LEFT JOIN author_info i ON a.author_id = i.id LEFT JOIN base_user u ON i.user_id = u.id WHERE a.contribution_id = '1173891793412005889' GROUP BY a.author_id)这个sql查询执行会报错,报错信息为(Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ctsnews.a.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ),该如何修改才能正常查询
时间: 2024-02-14 12:29:03 浏览: 61
contributors-on-github-master.zip_github_show_stats contributor
错误信息提示你的查询中使用了 GROUP BY,但 SELECT 列表中的第一个表达式(a.*)不在 GROUP BY 子句中,并且它包含了一个非聚合列(ctsnews.a.id),该列在 GROUP BY 子句中没有函数依赖关系。这与当前的 sql_mode=only_full_group_by 不兼容。
要解决这个问题,你可以修改查询,将非聚合列从 SELECT 列表中移除,或者将其包含在 GROUP BY 子句中。以下是两种修改的选择:
1. 移除非聚合列:
```sql
SELECT
a.author_id,
i.bank_card,
i.open_bank,
u.id_card
FROM
`payment_contribution_detail` a
LEFT JOIN author_info i ON a.author_id = i.id
LEFT JOIN base_user u ON i.user_id = u.id
WHERE
a.contribution_id = '1173891793412005889'
GROUP BY a.author_id
```
这个修改将 SELECT 列表中的非聚合列 `a.*` 替换为 `a.author_id`,并且移除了 GROUP BY 子句中的 `a.*`,因为在这个查询中,你只需要作者的相关信息,并不需要所有的 `payment_contribution_detail` 列。
2. 将非聚合列包含在 GROUP BY 子句中:
```sql
SELECT
a.*,
i.bank_card,
i.open_bank,
u.id_card
FROM
`payment_contribution_detail` a
LEFT JOIN author_info i ON a.author_id = i.id
LEFT JOIN base_user u ON i.user_id = u.id
WHERE
a.contribution_id = '1173891793412005889'
GROUP BY a.author_id, a.id, a.column1, a.column2, ...
```
这个修改将 GROUP BY 子句中的 `a.author_id` 之后添加了其他 `payment_contribution_detail` 表中的列,你需要将这些列逐一列出,以确保 GROUP BY 子句中包含了所有非聚合列。
请注意,第二种修改方式可能更适用于你的需求,因为它可以保留并显示 `payment_contribution_detail` 表中的所有列。你需要将 `...` 替换为实际的列名。
阅读全文