下面的SQL语句执行时，MySQL提示问题：
“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.APPLY_NO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”
select
a.APPLY_NO as "APPLY_NO", a.ID_CARD_NO as "ID_CARD_NO",
r.DEDUCT_NO as "DEDUCT_NO", r.BANK_NO as "BANK_NO", r.REPAY_DATE as "REPAY_DATE", sum(r.`AMOUNT`) as "AMOUNT"
from (
select * from APPLY_ORDER
) a
right join REPAYMENT_RECORD r on a.APPLY_NO = r.APPLY_NO
group by r.DEDUCT_NO;
原因是在sql_mode=only_full_group_by时，group by语句中必须列举出所有未应用聚合函数的列。
避免这个问题的方法有两个：
- 修改mysql的配置文件，去掉only_full_group_by的限制（方法可以参考link）。
或 - 将所有未应用聚合函数的列加在group by后面。
第2个方法也不是绝对的，只要在group by后面列出表的唯一索引即可：
select
a.APPLY_NO as "APPLY_NO", a.ID_CARD_NO as "ID_CARD_NO",
r.DEDUCT_NO as "DEDUCT_NO", r.BANK_NO as "BANK_NO", r.REPAY_DATE as "REPAY_DATE", sum(r.`AMOUNT`) as "AMOUNT"
from (
select * from APPLY_ORDER
) a
right join REPAYMENT_RECORD r on a.APPLY_NO = r.APPLY_NO
group by r.DEDUCT_NO, r.BANK_NO, r.ACCOUNT_TYPE, r.REPAY_DATE, a.APPLY_NO;
APPLY_NO是APPLY_ORDER的唯一索引，能够保证记录的唯一性。所以在group by后面加了APPLY_NO就不用追加APPLY_ORDER的其他列了。
由于REPAYMENT_RECORD表没有唯一索引，所以要追加此表在select中没有应用聚合函数的列。
