oracle 同时使用order by 和 group by 报不是一组表达式

Question

SELECT
*
FROM
(
SELECT
row_.*, ROWNUM rownum_
FROM
(
SELECT
edv.CLASS_CODE virtualClassCode,
edv.CLASS_NAME virtualClassName,
edv.MASTER_TEACHER_EMAIL masterEmail,
edv.MASTER_TEACHER_NAME masterName,
edv.SUBJECT_NAME subjectName,
TO_CHAR (
edv.START_DATE,
'YYYY-MM-DD'
) startDate,
TO_CHAR (edv.END_DATE, 'YYYY-MM-DD') endDate,
edv.LESSON_TIME lessonTime,
edv.LESSON_COUNT lessonCount,
TO_CHAR (
edv.CREATE_TIME,
'YYYY-MM-DD HH24:MI:SS'
) createTime,
edv.CREATE_BY creatorId,
edv. ID vId,
edv.CREATOR_NAME creatorName,
edv.MODIFY_ID modifyId,
edv.MODIFY_NAME modifyName,
edv.IS_ONLINE isOnLine,
TO_CHAR (
edv.MODIFY_TIME,
'YYYY-MM-DD HH24:MI:SS'
) modifyTime
FROM
EXT_DT_VIRTUAL_CLASS edv
 LEFT JOIN EXT_DT_VIRTUAL_BIND_REAL_CLASS bc ON EDV."ID" = BC.VIRTUAL_CLASS_ID
 LEFT JOIN EXT_DT_VIRTUAL_LESSON edl ON EDV."ID"=edl.VIRTUAL_CLASS_ID
WHERE
1 = 1
GROUP BY edv."ID",edv.CREATE_TIME  
ORDER BY
edv.CREATE_TIME DESC ) row_
WHERE 

ROWNUM <= 10
)
WHERE
rownum_ > 0
 
报:[Err] ORA-00979: not a GROUP BY expression

Answer 1

没有聚合函数无须使用group by，硬是要使用，就要group by 所有字段.
改了下SQL，你执行下试试看：select *
from(select t.*, rownum rn
              from(select edv.class_code virtualclasscode,
                                        edv.class_name virtualclassname,
                                        edv.master_teacher_email masteremail,
                                        edv.master_teacher_name mastername,
                                        edv.subject_name subjectname,
                                        to_char (edv.start_date,'yyyy-mm-dd') startdate,
                                        to_char (edv.end_date, 'yyyy-mm-dd') enddate,
                                        edv.lesson_time lessontime,
                                        edv.lesson_count lessoncount,
                                        to_char (edv.create_time,'yyyy-mm-dd hh24:mi:ss') createtime,
                                        edv.create_by creatorid,
                                        edv. id vid,
                                        edv.creator_name creatorname,
                                        edv.modify_id modifyid,
                                        edv.modify_name modifyname,
                                        edv.is_online isonline,
                                        to_char (edv.modify_time,'yyyy-mm-dd hh24:mi:ss') modifytime
                              from  ext_dt_virtual_class edv
                         left join ext_dt_virtual_bind_real_class bc on edv.id = bc.virtual_class_id
                         left join ext_dt_virtual_lesson edl on edv.id=edl.virtual_class_id
                      order by edv.create_time desc
            ) t
   where  rownum <= 10
        )
where rn  > 0

Answer 2

edv. id vid 之间有空格，需要改成: edv.id vid