group by 问题

我的sql类似这样:
SELECT CASE WHEN C.LOGONID IS NULL THEN 0 ELSE 1 END AS CHECKED
FROM ${schema}.ECAS_APPL A
JOIN ${schema}.ECAS_ROLE B
ON A.APPLID = B.APPLID
LEFT JOIN ${schema}.ECAS_USERROLE C
ON A.APPLID = C.APPLID
AND B.ROLEID = C.ROLEID
AND C.LOGONID = #loginId#
AND C.BANKID = #bankId#
GROUP BY CASE WHEN C.LOGONID IS NULL THEN 0 ELSE 1 END

报错’C.LOGONID’ is not in GROUP BY,报错图如下


按文档中提到的group by算子,HASH GROUP BY应该就支持才对吧,是需要做什么特殊配置嘛?求解~

2 个赞

SHOW VARIABLES like ‘version_comment’; 查一下数据库的版本号

学习一下

环境上暂时无法执行,按客户安装说明,版本应该是OceanBaseDB 4.2.1,是版本问题嘛

登录数据库,修改sql_mode参数取值,去掉ONLY_FULL_GROUP_BY条件,忽略GROUP BY的严格检查试试

主要另外个环境正常,想不改这个参数解决掉 :joy:

可能另外的环境,驱动在连接数据时自动改了sql_mode.

GROUP BY 还能这样用,学习到了