ColdFusion共享环境下如何规避或禁用SQL模式ONLY_FULL_GROUP_BY？

1. 会话级临时禁用ONLY_FULL_GROUP_BY #

在同一个<cfquery>中先执行会话级的SQL模式修改，仅影响当前数据库连接，不会改动全局配置：

<cfquery name='test' datasource='whatever'>
    SET sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
    select  PersActExt,PersActPaid,PersActMdate,PersActbl,
            PersTrId,PersTrMas,PersTrSub,PersTrCode,
            MAX(PersTrPaid),  MAX(PersTrDate)
    from PersActiv
    left join PersTrans on
         PersTrId = PersActId and
         PersTrMas = PersActMas and
         PersTrSub = PersActSub and
         PersTrCode = PersActCode
    where PersActMas = 'bill'
    group by PersTrId,PersTrMas,PersTrSub,PersTrCode
</cfquery>

若数据库驱动不允许单个<cfquery>执行多语句，可将SET语句单独拆分，并用<cftransaction>包裹确保使用同一连接：

<cftransaction>
    <cfquery datasource='whatever'>
        SET sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
    </cfquery>
    <cfquery name='test' datasource='whatever'>
        select  PersActExt,PersActPaid,PersActMdate,PersActbl,
                PersTrId,PersTrMas,PersTrSub,PersTrCode,
                MAX(PersTrPaid),  MAX(PersTrDate)
        from PersActiv
        left join PersTrans on
             PersTrId = PersActId and
             PersTrMas = PersActMas and
             PersTrSub = PersActSub and
             PersTrCode = PersActCode
        where PersActMas = 'bill'
        group by PersTrId,PersTrMas,PersTrSub,PersTrCode
    </cfquery>
</cftransaction>

2. 用ANY_VALUE()函数适配ONLY_FULL_GROUP_BY #

MySQL 5.7及以上版本支持ANY_VALUE()函数，它明确告知数据库：分组后指定字段在每组内值一致，返回任意一个即可，正好匹配你场景中PersAct表单条记录的特性，无需给所有字段加MAX：

<cfquery name='test' datasource='whatever'>
    select  ANY_VALUE(PersActExt), ANY_VALUE(PersActPaid), ANY_VALUE(PersActMdate), ANY_VALUE(PersActbl),
            PersTrId,PersTrMas,PersTrSub,PersTrCode,
            MAX(PersTrPaid),  MAX(PersTrDate)
    from PersActiv
    left join PersTrans on
         PersTrId = PersActId and
         PersTrMas = PersActMas and
         PersTrSub = PersActSub and
         PersTrCode = PersActCode
    where PersActMas = 'bill'
    group by PersTrId,PersTrMas,PersTrSub,PersTrCode
</cfquery>

3. 重构查询避免GROUP BY冲突 #

先通过子查询聚合PersTrans表的MAX值，再关联PersActiv表，主查询无需GROUP BY，完全符合ONLY_FULL_GROUP_BY规则：

<cfquery name='test' datasource='whatever'>
    select  pa.PersActExt, pa.PersActPaid, pa.PersActMdate, pa.PersActbl,
            pt.PersTrId, pt.PersTrMas, pt.PersTrSub, pt.PersTrCode,
            pt.MaxPersTrPaid, pt.MaxPersTrDate
    from PersActiv pa
    left join (
        select  PersTrId, PersTrMas, PersTrSub, PersTrCode,
                MAX(PersTrPaid) as MaxPersTrPaid,
                MAX(PersTrDate) as MaxPersTrDate
        from PersTrans
        group by PersTrId, PersTrMas, PersTrSub, PersTrCode
    ) pt on
         pt.PersTrId = pa.PersActId and
         pt.PersTrMas = pa.PersActMas and
         pt.PersTrSub = pa.PersActSub and
         pt.PersTrCode = pa.PersActCode
    where pa.PersActMas = 'bill'
</cfquery>

内容的提问来源于stack exchange，提问作者Betty Mock