oracle 根据一个日期查询一周的数据sql

--查询所有数据里面自己的排名第几（下面是指  自己在本周所有数据中排名第几）
select tt.rn 
  from (select t.*, row_number() over(order by start_time desc) rn
          from tb_match_phase_user t
         where t.start_time between
               TRUNC(TO_DATE('2015-04-02', 'YYYY-MM-DD'), 'IW') and
               TRUNC(TO_DATE('2015-04-02', 'YYYY-MM-DD'), 'IW') + 6) tt
 where tt.user_id = 17102133

 

SELECT TRUNC(TO_DATE('2015-04-08', 'YYYY-MM-DD'), 'IW') AS STARTDATE
  FROM DUAL; --本周周一
SELECT TRUNC(TO_DATE('2015-04-08', 'YYYY-MM-DD'), 'IW') + 6 AS ENDDATE
  FROM DUAL; --本周周日

select *
  from tb_match_phase_user
 where start_time between TRUNC(TO_DATE('2015-04-02', 'YYYY-MM-DD'), 'IW') and
       TRUNC(TO_DATE('2015-04-02', 'YYYY-MM-DD'), 'IW') + 6 --查询一周的数据