반응형
1. WHERE 절에는 집계함수를 사용할 수 없다.
-> GROUP BY절의 HAVING절에서 그룹 표현식으로 사용
-> 서브쿼리를 이용
2. DISTINCT의 위치는 사용하고자 하는 변수 바로 앞에 위치 시킨다.
-> SELECT COUNT(DISTINCT NAME)
3. GROUP BY 절을 사용할 때 SELECT절에 사용될 수 있는 컬럼이 제한적이다.
-> 즉, GROUP BY 절에서 사용한 컬럼과 그룹함수만 사용될 수 있다.
-> GROUP BY 식에서 사용한 컬럼이 TO_CHAR() 함수에 의해 값이 변경되었다면 변형된 형태로 SELECT 절에 사용되어야 한다.
4. 페이징 기법 ROWNUM과 FETCH 구문 [오라클12이상]
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회
(1) 인라인 뷰를 이용해, NOT IN과 ROWNUM 사용 [ROWNUM을 사용하기 위해선 서브쿼리 필요]
SELECT NAME, DATETIME
FROM (SELECT NAME, DATETIME
FROM ANIMAL_INS A
WHERE A.ANIMAL_ID NOT IN (SELECT B.ANIMAL_ID FROM ANIMAL_OUTS B)
ORDER BY DATETIME)
WHERE ROWNUM <=3(2) JOIN을 이용해, FETCH FIRST 사용 [MySQL의 LIMIT]
SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.DATETIME IS NULL
ORDER BY INS.DATETIME
FETCH NEXT 3 ROWS ONLY --오라클12C이상
①ROWNUM 구문
: 서브쿼리로 조회 후, ROWNUM으로 칼럼 수 제한
②FETCH 구문 [MYSQL의 LIMIT 구문]
: OFFSET 옵션
OFFSET 자리에는 원하는 row limits 이전에 스킵하고 싶은 row 사용
OFFSET에 음수가 오는 경우 Oracle은 0으로 인식하며,
NULL이 오거나 리턴되는 row 개수보다 큰 수를 쓸 경우 아무 행도 리턴되지 않는다.
[OFFSET 숫자 ROWS]
FETCH NEXT [로우 수|퍼센트] ROWS [ONLY | WITH TIES]
FETCH FIRST [로우 수|퍼센트] ROWS [ONLY | WITH TIES]OFFSET 10 ROWS --10개 로우 스킵
FETCH NEXT 10 ROWS ONLY --그 다음 10개만 출력
FETCH NEXT 10 ROWS WITH TIES --맨 마지막 행도 추가로 출력
FETCH FIRST 5 PERCENT ROWS ONLY --총 결과 로우 수에 따라 해당 퍼센트 출력
5. 조인의 방법
① FROM 조인 -> 테이블1 , 테이블2 / 테이블1 JOIN 테이블2 WHERE 테이블1.칼럼1 = 테이블2.칼럼1
② LEFT, RIGHT 조인 -> 테이블1 LEFT JOIN 테이블2 ON이나 USING 필수
6. DECODE와 CASE의 특징
DECODE(RAW,'%법','1','2') -> DECODE로는 LIKE 불가능
-> CASE WHEN RAW LIKE '%법' TEHN '1' ELSE '2'
THEN 결과 값 앞에 이어 붙이거나 공백을 넣고 싶을땐
-> CASE WHEN RAW LIKE '%법' THEN ' '||'1' ELSE '2' ||
SELECT ANIMAL_ID, NAME,
CASE WHEN SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
ELSE 'X'
END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성
이때 결과는 보호 기간이 긴 순으로 조회
7. 기간의 경과에 따라 나열
(1) ROWNUM 이용
(2) FETCH 구문 이용
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS INS, ANIMAL_OUTS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY OUTS.DATETIME - INS.DATETIME DESC
FETCH FIRST 2 ROWS ONLY
8. FROM절 서브쿼리 (인라인뷰)사용시 서브쿼리에 ALIAS를 꼭 붙여야 한다.
9. 도시 이름이 가장 긴/짧은 도시이름과 길이를 출력
SELECT CITY, LENGTH(CITY)
FROM (SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY))FROM STATION)ORDER BY CITY)
WHERE ROWNUM<=1;
SELECT CITY, LENGTH(CITY)
FROM (SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY))FROM STATION)ORDER BY CITY)
WHERE ROWNUM<=1;
10.대소문자 구분 없이 LIKE이용하는 방법
SELECT DISTINCT CITY
FROM STATION
WHERE
LOWER(CITY) LIKE 'a%' OR LOWER(CITY) LIKE 'e%' OR
LOWER(CITY) LIKE 'i%' OR LOWER(CITY) LIKE 'o%' OR
LOWER(CITY) LIKE 'u%';
11. 가상 칼럼을 이용해 결과값이 없는 칼럼에 0을 만들어 추가해준다.
-> 계층형 질의 이용
| 가상 칼럼 | 설명 |
| LEVEL | 루트 데이터이면 1, 그 하위 데이터이면 2. 리프 데이터까지 1씩 증가한다. |
| CONNECT_BY_ISLEAF | 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0 |
| CONNECT_BY_ISCYCLE | 전개 과정에서 자식을 갖는데, 조상으로 존재시 1, 그렇지 않으면 0. 여기서 조상은 자신으로부터 루트까지 경로에 존재하는 데이터 CYCLE 옵션 사용시에만 사용 가능 |
| 함수 | 설명 |
| SYS_CONNECT_BY_PATH | 루트 데이터부터 현재 전개할 데이터 까지 경로 표시 SYS_CONNECT_BY_PATH(칼럼, 경로분리자) |
| CONNECT_BY_ROOT | 현재 전개할 데이터의 루트 데이터 표시 [단항 연산자] CONNECT_BY_ROOT 칼럼 |
| 부모와 자식 관계 | 설명 |
| CONNECT BY PRIOR 자식 = 부모 | 관리자 -> 사원 방향을 전개 순방향 전개 |
| =CONNECT BY 부모 = PRIOR 자식 | |
| CONNECT BY PRIOR 부모 = 자식 | 사원 -> 관리자 방향을 전개 역방향 전개 |
| =CONNECT BY 자식 = PRIOR 부모 |
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원,
관리자, CONNECT_BY_ISLEAF ISLEAF
FROM 사원 START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자;LEVEL 사원 관리자 ISLEAF
----- -------- ----- ------
1 A 0
2 B A 1
2 C A 0
3 D C 1
3 E C 1
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원,
관리자, CONNECT_BY_ISLEAF ISLEAF
FROM 사원 START WITH 사원 = 'D'
CONNECT BY PRIOR 관리자 = 사원;LEVEL 사원 관리자 ISLEAF
----- -------- ----- ------
1 D C 0
2 C A 0
3 A 1
| START WITH 칼럼 [='~' | IS NULL] | 상하 관계 질의 시작 칼럼 설정 |
| CONNECT_BY_LEVEL | 연속된 숫자 조회시 사용 |
--1~10까지 연속된 숫자 조회
SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <=10--2021년 1월부터 12월까지 출력
SELECT '2020 년 '||LPAD(LEVEL, 2, 0)||'월' AS NO
FROM DUAL
CONNECT BY LEVEL <=12--특정 날짜 구간으로 조회
SELECT TO_DATE('20200701', 'YYYYMMDD') + (LEVEL-1) AS DT
FROM DUAL CONNECT BY LEVEL <= (TO_DATE('20200717', 'YYYYMMDD') - TO_DATE('20200701', 'YYYYMMDD')) + 1
---------------------------------------------------------------------------------------------
WITH WD AS
( SELECT TO_DATE('20200701', 'YYYYMMDD')
AS FROM_DT -- 시작일자 ,
TO_DATE('20200717', 'YYYYMMDD') AS TO_DT -- 종료일자
FROM DUAL )
SELECT DT, DT2
FROM ( SELECT TO_CHAR(FROM_DT + (LEVEL-1), 'YYYYMMDD') AS DT ,
TO_CHAR(FROM_DT + (LEVEL-1), 'YYYY-MM-DD') AS DT2
FROM WD CONNECT BY LEVEL <= (TO_DT - FROM_DT) + 1 )--데이터 복제 [카테시안 곱 이용]
SELECT Y.NO, X.*
FROM(
SELECT *
FROM EMP e
)X, (
SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL<=2
)Y
ORDER BY ENAME,JOB,NO
SELECT HOUR, COUNT(*)-1 COUNT
FROM
(
(SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) HOUR FROM ANIMAL_OUTS)
UNION ALL
(SELECT LEVEL-1 FROM DUAL CONNECT BY LEVEL <=24)
)
GROUP BY HOUR
ORDER BY HOUR
--COUNT에 -1를 안넣어 주는 경우 -> 추가한 가상 칼럼의 수까지 포함해 +1이 된 결과가 출력
SELECT HOUR, COUNT(*) COUNT
FROM
(
(SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) HOUR FROM ANIMAL_OUTS)
UNION ALL
(SELECT LEVEL-1 FROM DUAL CONNECT BY LEVEL <=24)
)
GROUP BY HOUR
ORDER BY HOUR
| HOUR | COUNT |
| 0 | 1 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 4 |
| 8 | 2 |
| 9 | 2 |
| 10 | 3 |
| 11 | 14 |
| 12 | 11 |
| 13 | 15 |
| 14 | 10 |
| 15 | 8 |
| 16 | 11 |
| 17 | 13 |
| 18 | 17 |
| 19 | 3 |
| 20 | 1 |
| 21 | 1 |
| 22 | 1 |
| 23 | 1 |
--LEVEL에 -1를 안넣어 주는 경우 -> 0시부터가 아닌 1시부터로 출력 [LEVEL은 0부터 시작하기 때문]
SELECT HOUR, COUNT(*)-1 COUNT
FROM
(
(SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) HOUR FROM ANIMAL_OUTS)
UNION ALL
(SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=24)
)
GROUP BY HOUR
ORDER BY HOUR
| HOUR | COUNT |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 3 |
| 8 | 1 |
| 9 | 1 |
| 10 | 2 |
| 11 | 13 |
| 12 | 10 |
| 13 | 14 |
| 14 | 9 |
| 15 | 7 |
| 16 | 10 |
| 17 | 12 |
| 18 | 16 |
| 19 | 2 |
| 20 | 0 |
| 21 | 0 |
| 22 | 0 |
| 23 | 0 |
| 24 | 0 |
계층형 질의에서 CASE 구문을 이용한 LEAF, ROOT, INNER 출력
SELECT N, CASE WHEN LEVEL = 1 THEN 'Root'
WHEN CONNECT_BY_ISLEAF = 1 THEN 'Leaf'
ELSE 'Inner' END NODE
FROM BST START WITH P IS NULL CONNECT BY PRIOR N=P
ORDER BY N;1 Leaf
2 Inner
3 Leaf
4 Inner
5 Leaf
6 Inner
7 Leaf
8 Leaf
9 Inner
10 Leaf
11 Inner
12 Leaf
13 Inner
14 Leaf
15 Root
12. ROW를 COLUMN으로 변환 [피벗을 이용한 출력]
1. LISTAGG [오라클 11g이상, 정렬 가능한 함수]
Grouping 하고자하는 칼럼을 Group By 로 묶은후 가로로 나열하고자 하는 칼럼을 Listagg에 명시
-> Within Group 에서 나열 순서를 Order BY로 지정
LISTAGG[(,]) WITHIN GROUP(ORDER BY) [OVER (PARTITION BY)]
--1. GROUP BY로 묶는 경우
--2. GROUP BY로 묶지 않는 경우1) SQL
SELECT DEPTNO, LISTAGG(ENAME, ‘,‘) WITHIN GROUP(ORDER BY HIREDATE) AS AGG_ENAME
FROM EMP
GROUP BY DEPTNO;
2) SQL
SELECT DEPTNO, ENAME, HIREDATE,
LISTAGG(ENAME, ‘,‘) WITHIN GROUP(ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGG_ENAME
FROM EMP;
WITH SOURCE
AS (
SELECT 'SAMSUNG' AS Company, 'Galaxy 20' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Fold' AS Phone FROM DUAL UNION ALL
SELECT 'SAMSUNG' AS Company, 'Galaxy Z Flip' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 11' AS Phone FROM DUAL UNION ALL
SELECT 'APPLE' AS Company, 'iPhone 10' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V60' AS Phone FROM DUAL UNION ALL
SELECT 'LG' AS Company, 'V50' AS Phone FROM DUAL
)
SELECT COMPANY, LISTAGG(PHONE,',') WITHIN GROUP(ORDER BY PHONE) AS PHONE
FROM SOURCE
GROUP BY COMPANY
ORDER BY COMPANY DESC;
2. PIVOT 함수
-SUM(DECODE)방식으로 내부적으로 처리 -> UNPIVOT도 지원
1) SQL
select *
from (select d.dname, e.job, e.empno
from emp e, dept d
where e.deptno = d.deptno)
pivot ( count(empno) for job in
('CLERK' clerk ,'MANAGER' manager,'PRESIDENT' president, 'ANALYST' analyst, 'SALESMAN' saleman)
);
2) PIVOT 사용
Select * from t1 pivot ( sum(c3) for c2 in (1,2,3,4,5) );
3) PIVOT 내부
Select c1,
sum(case when (c2 = 1) then c3 end) as "1",
sum(case when (c2 = 2) then c3 end) as "2",
sum(case when (c2 = 3) then c3 end) as "3",
sum(case when (c2 = 4) then c3 end) as "4",
sum(case when (c2 = 5) then c3 end) as "5"
from t1
group by c1;
13. 조인과 DISTINCT의 위치
-> 아우터 조인시 ON 필수로 필요한데, 여러 개 조인시,
COUNT 사용시 DISTINCT : COUNT(DISTINCT(~))
FROM 원 테이블 JOIN 추가 테이블 ON (추가 조건)SELECT CP.COMPANY_CODE, CP.FOUNDER,
COUNT(DISTINCT(LM.LEAD_MANAGER_CODE)),COUNT(DISTINCT(SM.SENIOR_MANAGER_CODE)),
COUNT(DISTINCT(MGR.MANAGER_CODE)), COUNT(DISTINCT(EMP.EMPLOYEE_CODE))
FROM COMPANY CP
JOIN LEAD_MANAGER LM ON(CP.COMPANY_CODE=LM.COMPANY_CODE)
JOIN SENIOR_MANAGER SM ON(LM.COMPANY_CODE=SM.COMPANY_CODE)
JOIN MANAGER MGR ON(SM.COMPANY_CODE=MGR.COMPANY_CODE)
JOIN EMPLOYEE EMP ON( MGR.COMPANY_CODE=EMP.COMPANY_CODE)
GROUP BY CP.COMPANY_CODE,CP.FOUNDER
ORDER BY CP.COMPANY_CODE;
14. 같은 칼럼 내 다른 값과 비교 즉, X1=Y2 AND X2=Y1.
-> 셀프 조인 이용
내부 조인(Inner Join)을 사용할 경우 조인할 대상이 존재하지 않다면, 해당 데이터는 결과에서 누락 된다.
이를 방지하기 위해서는 아우터 조인을 사용해야 한다.
[LEFT JOIN | RIGHT JOIN] -> 조인의 기본값은 INNER JOIN으로 OUTER JOIN의 기본값은 LEFT OUTER JOIN
SELECT F1.X, F1.Y
FROM FUNCTIONS F1 INNER JOIN FUNCTIONS F2
ON F1.X=F2.Y AND F2.X=F1.Y
GROUP BY F1.X, F1.Y
HAVING COUNT(*) >1 OR F1.X<F1.Y
ORDER BY F1.X;
15. ROUND함수 사용법 ROUND( 칼럼, 반올림 계수)
-> 반올림 계수는 -일 경우 10의 제곱으로, +일 경우 10의 음의 자리로 반올림
-> 날짜 반올림도 가능 [정오 기준 반올림]
SELECT ROUND(to_date('2021-12-12 10:50', 'yyyy-mm-dd hh24:mi')) dte_am
, ROUND(to_date('2021-12-12 12:30', 'yyyy-mm-dd hh24:mi')) dte_pm
FROM dual
16.
(1) 8 보다 낮은 등급을 받은 학생 이름은 NULL
(2) 보고서는 등급별로 내림차순이어야 합니다. 즉, 더 높은 등급이 먼저 입력
(3) 같은 학년(8-10)이 배정된 학생이 한 명 이상인 경우 해당 학생의 이름을 알파벳순으로 정렬
(4) 등급별로 내림차순으로 나열합니다. 같은 등급 두 명 이상인 경우 점수에 따라 오름차순으로 정렬
SELECT CASE WHEN GRADE<8 THEN 'NULL' ELSE NAME END, GRADE , MARKS
FROM STUDENTS STD, GRADES GRD
WHERE GRD.MIN_MARK<=STD.MARKS AND STD.MARKS<=GRD.MAX_MARK
ORDER BY GRADE DESC, NAME,MARKS ASC;
17. GROUP BY에선 ALIAS 지정 불가 / GROUP BY 묶는 칼럼과 SELECT 칼럼은 가급적 동일하게
SELECT HACKER_ID, NAME
FROM SUBMISSIONS SU
INNER JOIN CHALLENGES CH ON SU.CHALLENGE_ID=CH.CHALLENGE_ID
INNER JOIN DIFFICULTY DI ON CH.DIFFICULTY_LEVEL=DI.DIFFICULTY_LEVEL
INNER JOIN HACKERS HA ON SU.HACKER_ID=HA.HACKER_ID
WHERE SU.SCORE=DI.SCORE
GROUP BY HACKER_ID, NAME
HAVING COUNT(SU.HACKER_ID)>=2
ORDER BY COUNT(SU.HACKER_ID) DESC, HACKER_ID ASC;18.
SELECT HA.HACKER_ID AS ID, NAME AS NAME,COUNT(CHALLENGE_ID) AS COUNT
FROM HACKERS HA
INNER JOIN CHALLENGES CH ON (CH.HACKER_ID=HA.HACKER_ID)
GROUP BY HA.HACKER_ID, NAMEHAVING MAX(COUNT(CHALLENGE_ID))=COUNT(CHALLENGE_ID);
-- 그룹함수는 하나의 칼럼에 2번까지 중첩 가능SELECT HA.HACKER_ID AS ID, NAME AS NAME, COUNT(CHALLENGE_ID) AS CHCOUNT
FROM HACKERS HA
INNER JOIN CHALLENGES CH ON (CH.HACKER_ID=HA.HACKER_ID)
GROUP BY HA.HACKER_ID, NAME
HAVING COUNT(CHALLENGE_ID)=(SELECT MAX(COUNT(CHALLENGE_ID)) FROM CHALLENGES GROUP BY HACKER_ID)
ORDER BY COUNT(CHALLENGE_ID) DESC, HA.HACKER_ID;-> 하지만 이 SQL문은 같은 챌린지 수에서 최댓값을 구한게 아니라 전체 챌린지 수에서 최댓값을 구한 구문
-> 한번에 구현하려고 하면 안되는이유
따라서, 케이스 분류를 두가지로 나눠서 수행
(1) 동일한 챌린지 수가 하나만 있을 때
(2) 동일한 챌린지 수가 둘 이상일 때
--(1) 동일한 챌린지 수가 하나만 있을 때
SELECT CHALL.CHCOUNT
FROM (SELECT HACKER_ID, COUNT(*) CHCOUNT
FROM CHALLENGES
GROUP BY CHALLENGES.HACKER_ID) CHALL
GROUP BY CHALL.CHCOUNT
HAVING COUNT(*)=1;--최댓값 구하기
SELECT MAX(JOINCHALL.CHCOUNT)
FROM (SELECT COUNT(*) CHCOUNT
FROM HACKERS
INNER JOIN CHALLENGES ON HACKERS.HACKER_ID = CHALLENGES.HACKER_ID
GROUP BY HACKERS.HACKER_ID)JOINCHALL;
1) 첫번째 방법
SELECT HA.HACKER_ID, HA.NAME, COUNT(CH.CHALLENGE_ID) CHCOUNT
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME
HAVING
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH.CHALLENGE_ID)
)=1
OR
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))>1
AND COUNT(CH.CHALLENGE_ID)=(SELECT MAX(COUNT(CH.CHALLENGE_ID))
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME)
)
ORDER BY CHCOUNT DESC, HACKER_ID;첫번째 방법 분석
해커스 테이블과 챌린지 테이블을 등가조인 [아이디를 기준으로]
-> 아이디와 이름으로 그룹화
SELECT HA.HACKER_ID, HA.NAME, COUNT(CH.CHALLENGE_ID) CHCOUNT
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME
HAVING
(
SELECT
FROM
WHERE
GROUP BY
HAVING
)
OR
(
SELECT
FROM
WHERE
HAVING
)
ORDER BY
그룹 조건식으로 챌린지수의 수가
HAVING
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM
WHERE
GROUP BY
HAVING
)=1해커스 테이블의 아이디와 챌린지 테이블의 아이디가 같은 경우,
HAVING
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY
HAVING
)=1그룹화한 챌린지 수가 같은 경우가 하나 일 때와
HAVING
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH.CHALLENGE_ID)
)=1
OR
그룹 조건식으로 챌린지수의 수가
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM
WHERE
GROUP BY
HAVING
)해커스 테이블의 아이디와 챌린지 테이블의 아이디가 같은 경우, 해커스 아이디와 이름으로 그룹화
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING
)그룹 조건식으로 챌린지 아이디의 수와 챌린지 아이디의 수가 같은 경우
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))가 하나 이상이고,
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))>1
AND챌린지 수와 해커스 테이블의 해커스 아이디와 챌린지 테이블의 해커스 아이디가 같을 때
해커스 아이디와 이름으로 그룹화 한 최대 챌린지 수가 같은 경우
(
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
FROM HACKERS HA2, CHALLENGES CH2
WHERE HA2.HACKER_ID=CH2.HACKER_ID
GROUP BY HA2.HACKER_ID, HA2.NAME
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))>1
AND COUNT(CH.CHALLENGE_ID)=(SELECT MAX(COUNT(CH.CHALLENGE_ID))
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME)
)챌린지 수를 내림차순하고 해커스 아이디 순으로 정렬
(
23
(SELECT COUNT(COUNT(CH2.CHALLENGE_ID))
24
FROM HACKERS HA2, CHALLENGES CH2
25
WHERE HA2.HACKER_ID=CH2.HACKER_ID
26
GROUP BY HA2.HACKER_ID, HA2.NAME
27
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH2.CHALLENGE_ID))>1
28
AND COUNT(CH.CHALLENGE_ID)=(SELECT MAX(COUNT(CH.CHALLENGE_ID))
29
FROM HACKERS HA, CHALLENGES CH
30
WHERE HA.HACKER_ID=CH.HACKER_ID
31
GROUP BY HA.HACKER_ID, HA.NAME)
32
)
33
ORDER BY CHCOUNT DESC, HACKER_ID;
2) 두번째 방법
SELECT HA.HACKER_ID, HA.NAME, COUNT(CH.CHALLENGE_ID)
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME
HAVING COUNT(CH.CHALLENGE_ID) NOT IN
(SELECT COUNT(CHALLENGE_ID)
FROM CHALLENGES
WHERE HACKER_ID != HA.HACKER_ID
GROUP BY HACKER_ID
HAVING COUNT (CHALLENGE_ID)!=
(SELECT MAX(COUNT(CHALLENGE_ID))
FROM CHALLENGES
GROUP BY HACKER_ID))
ORDER BY COUNT(CH.CHALLENGE_ID) DESC, HACKER_ID;-> WHERE절에 조건을 기술한 등가조인
-> HACKER_ID와 NAME 기준으로 소그룹
-> 소그룹 조건으로 챌린지 수가 (서브쿼리)에 NOT IN인 경우
-> 이 서브쿼리는 HAVING절에 서브쿼리의 HAVING절에 서브쿼리로
-> 챌린지 수가 HACKER_ID (이 ID는 다음 그룹 조건식에 해당하는 HACKER_ID)
이 HACKER_ID와 HACKERS 테이블의 HACKER_ID와 같지 않은 경우를 나타낸다
-> 다음 그룹 조건식은 챌린지수의 최댓값과 같지 않은 경우
두번째 방법 분석
해커스 테이블과 챌린지 테이블을 등가조인 [아이디를 기준으로]
-> 아이디와 이름으로 그룹화
SELECT HA.HACKER_ID, HA.NAME, COUNT(CH.CHALLENGE_ID)
FROM HACKERS HA, CHALLENGES CH
WHERE HA.HACKER_ID=CH.HACKER_ID
GROUP BY HA.HACKER_ID, HA.NAME;
더보기
//결과값
69471 Michelle 50
37092 Jennifer 1
97338 Amy 1
5443 Paul 26
1632 Michael 2
61093 Mark 1
84085 Johnny 29
33177 Jane 21
81506 Deborah 4
87524 Norma 30
61206 Lillian 1
40841 Brian 4
41362 Joyce 2
87305 Kathleen 2
55678 Jerry 2
21813 Gerald 12
76312 Kathryn 2
30677 Keith 1
72609 Bobby 8
42260 Dennis 4
25990 Mildred 1
17729 Charles 3
29423 Shirley 4
81123 Michael 4
75678 Barbara 3
64398 Nicholas 3
80004 Carolyn 6
83823 Steve 5
16866 Dorothy 5
30131 Bobby 2
45773 Janet 2
1468 Bonnie 3
58209 Nicole 12
7537 David 1
2069 Robert 2
2245 Amy 2
73676 Linda 1
10 Rose 2
58086 Debra 1
26802 Paul 1
92906 Amy 2
36125 Julia 2
42433 Douglas 3
87885 Gregory 1
87040 Craig 1
37643 Philip 2
72311 Paul 2
93085 Laura 2
68187 George 2
11715 Louise 1
24531 Albert 2
61987 Brenda 2
86142 Douglas 1
30908 Helen 1
83308 Roy 1
17311 Andrew 1
31955 Harry 3
23428 Arthur 33
12766 Jacqueline 40
96716 Emily 50
90267 Edward 9
56338 Jose 1
52462 Nicholas 50
64036 Craig 50
25687 Joshua 2
95135 Lawrence 3
59527 Philip 3
4087 Jennifer 4
54461 Janet 19
3640 Carol 6
73267 Jeremy 1
90164 Jack 3
29542 Janet 12
19962 Patricia 1
43571 Amy 2
96773 Angela 1
66551 Elizabeth 2
72253 Edward 2
37065 Jeremy 3
61004 Philip 2
94278 Dennis 50
52640 Bruce 2
66456 Stephen 2
28766 Paul 1
2373 Maria 2
87118 Howard 3
99101 Timothy 1
22557 Andrew 2
14215 Debra 3
96635 Carlos 3
22455 Carolyn 1
99165 Nancy 1
63803 Carolyn 1
86203 Martha 3
32405 Marilyn 2
46468 Timothy 1
28180 Alan 3
2689 Joe 1
17419 Keith 3
10975 Christine 1
49357 Kenneth 2
47156 Kelly 1
32735 Cheryl 1
57873 Diana 1
88858 Bruce 1
17010 Marilyn 3
58543 Rachel 1
26332 Melissa 3
7961 Michelle 1
5827 Kelly 1
42277 Sara 18
39582 Maria 28
19835 Joyce 36
52965 Bobby 25
78091 Beverly 3
85834 Julie 3
41805 Benjamin 50
38824 Mark 2
24047 Elizabeth 1
55197 Daniel 2
54015 Carolyn 1
45855 Clarence 22
92268 Joyce 2
83182 Howard 2
37331 Charles 3
57556 Melissa 2
20077 Christine 3
17663 Benjamin 1
91620 Debra 1
74953 Rebecca 2
90276 Joyce 1
54483 Kathy 2
63104 Mark 2
87567 Phyllis 2
73018 Ruby 3
62858 Anna 2
53464 Bobby 3
88069 Jean 1
53451 Jeffrey 1
3740 Paula 4
23545 Cynthia 2
88534 Steve 2
61102 Kenneth 1
46144 Sharon 1
82190 Frances 2
20388 Christopher 2
88083 Anna 1
12466 Antonio 3
89514 Jeffrey 1
38540 Katherine 1
8505 Christina 2
85094 Matthew 1
37317 Lillian 2
61716 Lawrence 2
75719 Susan 2
6973 Gloria 2
43595 Adam 1
82100 Brenda 2
27797 Helen 1
20023 Brian 50
39618 John 3
86280 Beverly 37
28879 Steven 2
18425 Anna 50
20190 Aaron 1
29841 Charles 1
38316 Walter 35
76022 Sarah 2
10408 Jesse 3
63684 Randy 1
57790 Ryan 3
36562 Melissa 4
42460 Dennis 2
73799 Jessica 3
44488 Scott 3
57799 Margaret 4
78612 Phyllis 3
27277 Sandra 12
11019 Anthony 2
38035 Doris 12
78615 Kathryn 1
13203 Dorothy 2
40176 Brian 3
74320 Pamela 1
74746 Jeremy 2
88887 Antonio 3
82531 Mark 2
96579 Linda 6
72763 Julie 1
4586 David 2
84739 Alan 1
10068 Matthew 5
7846 Donna 4
64341 Virginia 1
65176 Janice 2
53597 Rose 1
17283 Nancy 4
19032 Andrew 1
7565 Joyce 2
50794 Larry 2
60071 Rachel 3
59748 Shirley 2
8549 Brandon 3
15145 Jonathan 2
25759 Norma 3
82994 Bonnie 2
45019 Louis 2
65981 Karen 3
52992 Deborah 3
17846 Alan 1
53768 Douglas 1
30778 Jose 1
8157 Walter 3
37764 Jimmy 1
81589 Julia 2
95437 George 32
9910 Marilyn 2
29483 Jeffrey 34
25638 Stephen 4
55727 Randy 12
5734 James 12
67538 Stephen 2
86419 Mark 7
40754 Bonnie 2
87862 Jerry 2
22496 Margaret 3
41632 Philip 3
42856 Clarence 3
84427 Cynthia 3
1195 Lisa 6
66641 Shirley 3
74126 Fred 3
65273 Douglas 2
53786 Brian 2
55415 Amy 1
5367 Kevin 5
55500 Kenneth 6
58358 Kevin 3
6011 Robin 1
54488 Catherine 5
10058 Lori 2
58167 David 1
29987 Kelly 2
80663 Ralph 3
97831 Gary 2
47921 Lillian 1
21916 Clarence 1
9642 Joseph 1
94676 Patrick 1
30893 Ann 3
21821 Carol 1
94337 Lillian 1
52789 Harry 3
78575 Jennifer 2
72321 Julie 1
56103 Kelly 1
3432 Linda 1
80491 Nicholas 3
17867 Tammy 2
3834 Marilyn 3
47069 Jose 2
88084 Alan 1
83407 Roy 2
90369 Christina 1
92239 Shirley 1
13075 John 1
54510 Paula 1
21350 Bobby 1
31770 Ashley 1
46963 Barbara 31
5120 Julia 50
83045 Ralph 3
87544 Wayne 2
40373 Kimberly 4
7302 Victor 20
72866 Eugene 42
79179 Roy 2
41157 Rebecca 5
31426 Carlos 15
65843 Thomas 27
67399 Chris 2
19781 Jesse 1
20838 Bobby 2
95010 Victor 14
77173 Mildred 50
31465 Ryan 12
14389 Willie 3
84537 Walter 3
27071 Gerald 10
28807 Chris 2
95829 Christine 3
27804 Larry 3
1804 Joe 4
76856 Emily 7
68178 Gloria 1
59455 Virginia 12
89811 Jane 2
89860 Larry 4
79612 Tina 1
52350 Teresa 1
94981 Clarence 2
25684 Alan 1
43398 Steve 1
1799 Todd 2
18709 James 1
11658 Kimberly 3
93011 Tina 4
98785 Rose 1
57195 Beverly 1
19251 Roy 2
94746 Adam 1
70916 Maria 2
66655 Matthew 2
8096 Stephanie 2
41617 Douglas 2
8125 Gerald 2
33489 Denise 1
47822 Clarence 2
75058 John 3
64882 Roy 1
45685 Bobby 1
61769 Marie 1
96521 Christine 1
32364 Julia 1
68569 Joan 2
50560 Brian 1
72168 Doris 2
1315 Kimberly 2
33069 Ruth 2
52050 Steve 12
33625 Jason 50
96905 Lawrence 5
14726 Emily 1
99388 Mary 16
13798 Evelyn 2
4148 Harry 5
10949 Timothy 2
92920 Louis 1
69386 Brandon 4
42467 Ernest 1
14393 Ann 3
84938 Judy 1
47793 Paul 2
77810 Theresa 4
81652 Albert 1
38123 Craig 2
44860 Samuel 3
60177 Brian 1
2289 Pamela 3
41094 Wayne 3
56039 Teresa 1
62563 Donald 2
20267 Dorothy 3
158 Frank 2
46604 Christina 1
14307 David 1
35709 Shawn 3
81559 Alan 2
29598 Albert 3
34385 Jonathan 2
63466 Stephanie 4
73175 Russell 2
38877 Barbara 2
58895 Janet 2
18839 Robin 2
12119 Paul 2
92879 Harold 2
90125 Jesse 2
63730 Sarah 1
93312 Keith 2
45978 Dorothy 2
43240 Diana 1
91 Angela 2
84703 Earl 2
13905 Evelyn 1
22396 Wayne 1
29242 Jennifer 1
77105 Diana 24
33787 Susan 23
62259 Sean 3
96009 Russell 50
52858 Harold 4
37068 Patrick 41
86127 Evelyn 4
66445 Carl 2
30401 Elizabeth 5
88875 Douglas 3
41031 Ruby 4
88951 Jeremy 5
86592 Samuel 2
76786 Michelle 4
89903 Katherine 1
51815 Karen 3
86937 Janet 2
70499 Dennis 1
79739 Anne 3
59895 Martha 1
10938 Martha 4
95808 Charles 2
33710 Rose 3
18939 Jean 3
68860 Frank 2
74432 Eric 2
33273 Sara 1
9901 Lawrence 1
63375 Bruce 2
63263 Dorothy 1
66381 Harry 2
32797 Susan 2
59871 Martin 1
11406 Paula 2
23812 Jerry 1
40792 Terry 2
29340 Bonnie 2
15109 Dorothy 1
17282 Norma 1
63961 Anna 1이 결과에서 그룹 조건식을 만족하는 챌린지 수에 포함 되지 않으면 출력
HAVING COUNT(CH.CHALLENGE_ID) NOT IN
(SELECT
FROM
WHERE
GROUP
HAVING
(SELECT
FROM
GROUP BY
ORDER BY그룹 조건식 :
해커스 테이블의 해커스아이디의 챌린지 수와 다른 경우에
HAVING COUNT(CH.CHALLENGE_ID) NOT IN
(SELECT COUNT(CHALLENGE_ID)
FROM CHALLENGES
WHERE HACKER_ID != HA.HACKER_ID
GROUP BY HACKER_ID
HAVING COUNT (CHALLENGE_ID)!=
(SELECT
FROM
GROUP BY
ORDER BY최대 챌린지 수를 가지는 해커스아이디의 챌린지 수가
HAVING COUNT(CH.CHALLENGE_ID) NOT IN
(SELECT COUNT(CHALLENGE_ID)
FROM CHALLENGES
WHERE HACKER_ID != HA.HACKER_ID
GROUP BY HACKER_ID
HAVING COUNT (CHALLENGE_ID)!=
(SELECT MAX(COUNT(CHALLENGE_ID))
FROM CHALLENGES
GROUP BY HACKER_ID))
ORDER BY COUNT(CH.CHALLENGE_ID) DESC, HACKER_ID;
19. 같은 칼럼비교 가능
HAVING COUNT(CH2.CHALLENGE_ID)=COUNT(CH.CHALLENGE_ID)
반응형
'Data Science > SQLP' 카테고리의 다른 글
| [SQLP] 1-3. 데이터 저장 구조 및 I/O 메커니즘 (0) | 2022.01.17 |
|---|---|
| [SQLP] 1-2. SQL 공유 및 재사용 (1) | 2022.01.13 |
| [SQLP] 1-1. SQL 파싱과 최적화 (0) | 2022.01.13 |
| [SQLP] 목차 (0) | 2022.01.13 |
| [SQLP] 조인 기본 문제 (0) | 2022.01.06 |