순위 매기기 (rank, rownum)

work time table

---

 

 

 

이들 데이터의 작업시간이 짧게 걸리는 시간 순서대로 1부터 15까지의 순위를 
매겨서 출력하시오.

SELECT * FROM t_worktime

 

 

---

 

▶ rownum

조회 순서를 매길때

조회 개수를 제한하는 경우 사용

 

----------rank 사용 

SELECT
            workcd_vc, time_nu
           ,rank() over(order by time_nu) rnk
  FROM t_worktime;
  
SELECT rownum rno, workcd_vc, time_nu FROM t_worktime
WHERE rownum < 4;




----------- rank 사용 안함

SELECT
             *
   FROM (
                SELECT rownum rno, workcd_vc, time_nu FROM t_worktime
                WHERE rownum < 4   
             )a,
             (
             SELECT rownum rno, workcd_vc, time_nu FROM t_worktime
                WHERE rownum < 4
             )b;
             
             
SELECT
             a.workcd_vc, a.TIME_NU,  count(b.workcd_vc)
   FROM (
                SELECT rownum rno, workcd_vc, time_nu FROM t_worktime
                WHERE rownum < 4   
             )a,
             (
             SELECT rownum rno, workcd_vc, time_nu FROM t_worktime
                WHERE rownum < 4
             )b
 WHERE a.time_nu >= b.time_nu
 GROUP BY a.workcd_vc, a.time_nu;