下面的SQL語句執(zhí)行時，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語句中必須列舉出所有未應(yīng)用聚合函數(shù)的列。
避免這個問題的方法有兩個：
- 修改mysql的配置文件，去掉only_full_group_by的限制（方法可以參考link）泣侮。
或 - 將所有未應(yīng)用聚合函數(shù)的列加在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中沒有應(yīng)用聚合函數(shù)的列。
