[Err] ORA-00979: not a GROUP BY expression

最新推荐文章于 2022-07-02 15:41:28 发布

Spring......

最新推荐文章于 2022-07-02 15:41:28 发布

阅读量3.7k

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/qq_27399407/article/details/73301694

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

最近在按照用户统计其完成的任务量与总任务量，在用group by是遇到了这个错误，错误的sql如下：

select q.CHECK_MAN_ID checkManId,u.USER_ALIAS,count(*) total,sum(CASE WHEN q.QUOTA_USER_STATUS='finished' THEN 1 ELSE 0 END) finished
from IF_QUOTA_USER q,WM_BASE_USER u
where u.USER_ID = q.CHECK_MAN_ID
GROUP by q.CHECK_MAN_ID

后来查到原因好像是group by后要把所有的查询字段写下来；即要把u.USER_ALIAS也写在group by 后
正确的语句：

select q.CHECK_MAN_ID checkManId,u.USER_ALIAS,count(*) total,sum(CASE WHEN q.QUOTA_USER_STATUS='finished' THEN 1 ELSE 0 END) finished
from IF_QUOTA_USER q,WM_BASE_USER u
where u.USER_ID = q.CHECK_MAN_ID
GROUP by q.CHECK_MAN_ID，u.USER_ALIAS