Oracle SQL分组依据to_char -不是分组依据表达式

qv7cva1a  于 2023-01-20  发布在  Oracle
关注(0)|答案(1)|浏览(126)

我想按dd-mm-yyyy分组,以显示working_hours每个雇员(人)每天,但我得到错误消息ORA-00979: not a GROUP BY expression,当我从GROUP BY中删除TO_CHAR时,它工作正常,但这不是我想要的,因为我想group by天,而不考虑小时,我在这里做错了什么?

SELECT   papf.person_number emp_id,
         to_char(sh21.start_time,'dd/mm/yyyy') start_time,
         to_char(sh21.stop_time,'dd/mm/yyyy') stop_time,
         SUM(sh21.measure) working_hours
    FROM per_all_people_f papf,
         hwm_tm_rec sh21
         
         

    WHERE ...

    GROUP BY 
         papf.person_number,
         to_char(sh21.start_time,'dd/mm/yyyy'),
         to_char(sh21.stop_time,'dd/mm/yyyy')

    ORDER BY sh21.start_time
fkaflof6

fkaflof61#

ORDER BY sh21.start_time

需要仅为SELECT子句中定义的列别名:

ORDER BY start_time

或使用GROUP BY子句中的表达式:

ORDER BY to_char(sh21.start_time,'dd/mm/yyyy')

如果使用sh21.start_time,则table_alias.column_name语法引用表中的基础列,而不是按此选择/分组。

相关问题