[SQLD 2-02] ★SELECT 1 FROM T2★DISTINCT(중복제거)★MINUS★집계함수 ROLLUP , CUBE,GROUPING SETS★

1. SELECT 1 FROM T2

SELECT * FROM T2;

T2 테이블

SELECT 1 FROM T2 X;

T2 테이블의 존재 여

2. DISTINCT(중복제거) , MINUS

SELECT * FROM T1;

T1 테이블

SELECT DISTINCT A.C1 FROM T1 A

중복제거한 T1 테이블

SELECT C1 FROM T1 MINUS SELECT C1 FROM T2;

==> MINUS 의 경우 중복제거

 

3.  테이블 데이터 변경하기

 

T1 테이블

UPDATE T1
SET C1 = CASE
           WHEN C1 = 1 AND ROWNUM = 1 THEN 1
           WHEN C1 = 1 AND ROWNUM = 2 THEN 2
           ELSE 2
         END;

==> ROWNUM을 활용하여 T1 테이블 값 변경하기

 

SELECT ROWNUM, T1.*
FROM T1;

ROWNUM

4.  ROLLUP , CUBE , GROUPING SETS

CREATE TABLE T1 (
    C1 VARCHAR2(1),
    C2 DATE,
    C3 NUMBER(1)
);

INSERT INTO T1 (C1, C2, C3) VALUES ('A', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('A', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('B', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('B', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('C', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('C', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);

T1 테이블

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY ROLLUP(C2, C1);

ROLLUP

ROLLUP 소계 ==> (C2 , C1) , C2 , () 로 집계

 

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY ROLLUP( (C1, C2));

ROLLUP

ROLLUP 소계 ==> (C1 , C2) , () 로 집계

 

==> 괄호로 묶기

 

 

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY CUBE(C1, C2);

CUBE

CUBE 소계 ==> (C1 , C1) , C1 , C2 , () 로 집계

 

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY GROUPING SETS ( (C1, C2) , C1 

 

 

GROUPING SETS 소계 ==> (C1 , C2) ,C1 ==> 순서대로

 

GROUPING SETS

5.  RANK , DENSE_RANK

RANK() ==> 동일순위 허용 0 ==> 1 2 2 4 

DENSE_RANK() ==> 동일순위 허용 0 ==> 1 2 2 3

https://knowallworld.tistory.com/410 

[SQLD 2-01] ★LEFT OUTER JOIN★RANK()★DENSE_RANK()★GROUPBY★LPAD()★UPDATE★UNION★UNION ALL★MINUS★INTERSECT★기