把日期转换为周数，TO_CHAR(sysdate, 'IW')

SELECT TO_CHAR(sysdate, 'IW') as week_number,

TO_CHAR(TRUNC(sysdate, 'IW'), 'YYYY-MM-DD') as week_start_date,

TO_CHAR(TRUNC(sysdate, 'IW') + 6, 'YYYY-MM-DD') as week_end_date

FROM dual;

函数处理成周。在group by

星期几：

SELECT

'星期' || decode(to_number(to_char(sysdate - 1, 'd')), 1, '一', 2, '二', 3, '三', 4, '四', 5, '五', 6, '六', 7, '日') AS week_day,

sysdate FROM DUAL;  

第多少周。拼接一下：

Select to_char(sysdate,'iw') FROM DUAL;

自己多加一列，返回一个纯数字的周，再用这个排序就行了