[今日のクエリ]DENSE RANK

4615 ワード
検索 sql 順位 テキストリンク 
SELECT ENAME
	  , JOB
      , SAL
      , RANK() OVER (ORDER BY SAL DESC) AS RANK,
      , DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');

出力結果


ENAME | JOB | SAL | RANK | DENSE_RANK
FORD | ANALYST | 3000 | 1 | 1
SCOTT | ANALYST | 3000 | 1 | 1
JONES | MANAGER | 2975 | 3 | 2
BLAKE | MANAGER | 2850 | 5 | 3
  • RANK関数は2つのランキング1位を有し、次いで3位
    • を直接出力する.
  • DENSE RANKは第2ビット出力
    • である.
    SELECT JOB
      , ENAME
      , SAL
      , DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS 순위
FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('1981/01/01', 'RRRR/MM/DD')
				   AND TO_DATE('1981/12/31', 'RRRR/MM/DD');

    出力結果


    JOB|ENAME|SAL|ランキング
    ANALYST | FORD | 3000 | 1
    CLERK | JAMES | 950 | 1
    MANAGER | JONSE | 2975 | 1
    MANAGER | BLAKE | 2850 | 2
    MANAGER | CLARK | 2450 | 3
    PRESIDENT | KING | 5000 | 1
    SELECT DENSE_RANK(2975) WITHIN GROUP (ORDER BY SAL DESC) 순위
	FROM EMP;

    出力結果


    順位
    3
    SELECT DENSE_RANK('81/11/17') WITHIN GROUP (ORDER BY HIREDATE ASC) 순위
	FROM EMP;

    出力結果


    順位
    9
    HappyLeetcode39:Factorial Trailing Zeroes
    2022-04-17~18 JavaScript