You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
17 lines
517 B
17 lines
517 B
USE Study
|
|
GO
|
|
|
|
-- 그룹별 순위 지정
|
|
SELECT ENAME, SALARY, DNO, RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) AS RANK_DEPT
|
|
FROM EMPLOYEE
|
|
|
|
-- 각 그룹 급여기준 2위인 사원의 부서정보, 이름, 급여, 순위 조회
|
|
SELECT *
|
|
FROM
|
|
(SELECT DNO, ENAME, SALARY, RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) AS RANK_VALUE
|
|
FROM EMPLOYEE) AS TEMP
|
|
WHERE RANK_VALUE = 2
|
|
|
|
-- 그룹별 집단 함수 사용
|
|
SELECT DISTINCT DNO, AVG(SALARY) OVER (PARTITION BY DNO) FROM EMPLOYEE
|
|
SELECT DISTINCT DNO, AVG(SALARY) FROM EMPLOYEE GROUP BY DNO |