-
Notifications
You must be signed in to change notification settings - Fork 0
/
20200924.sql
141 lines (112 loc) · 3.1 KB
/
20200924.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
conditional insert
all : 조건에 만족하는 모든 구만의 insert 실행
first : 조건에 만족하는 첫번 째 구문의 insert 만 실행;
INSERT FIRST
WHEN eno >= 9500 THEN
INTO emp_test VALUES (eno,enm)
WHEN eno >= 9000 THEN
INTO emp_test2 VALUES (eno,enm)
SELECT 9000 eno, 'brown' enm FROM dual UNION ALL
SELECT 9500, 'sally' FROM dual;
SELECT *
FROM emp_test;
SELECT *
FROM emp_test2;
--MERGE
EXPLAIN PLAN FOR
MERGE INTO emp_test
USING (SELECT 9000 eno, 'moon' enm FROM dual) a
ON (emp_test.empno = a.eno)
WHEN MATCHED THEN
UPDATE SET ename = a.enm
WHEN NOT MATCHED THEN
INSERT VALUES (a.eno, a.enm);
SELECT *
FROM table (dbms_xplan.display);
emp ==> emp_test 데이터 두건 복사
INSERT INTO emp_test
SELECT empno, ename
FROM emp
WHERE empno IN(7369,7499);
emp테이블을 이용하여 emp 테이블에 존재하고 emp_test에는 없는 사원에 대해서는 신규로
emp_test 테이블에 신규로 입력
emp,emp_test 양쪽에 존재하는 사원은 이름 || '_M';
MERGE INTO emp_test
USING emp
ON(emp.empno = emp_test.empno)
WHEN MATCHED THEN
UPDATE SET ename = emp_test.ename || '_M'
WHEN NOT MATCHED THEN
INSERT VALUES (emp.empno, emp.ename);
SELECT *
FROM emp_test, emp
WHERE emp.empno = emp_test.empno;
--report group function
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
UNION ALL
SELECT NULL, SUM(sal)
FROM emp;
SELECT deptno, sum(sal)
FROM emp
GROUP BY ROLLUP (deptno);
SELECT job, deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP (job,deptno);
--위에것을 풀면 밑에것이 된다.
SELECT job, deptno, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY job, deptno UNION ALL
SELECT job, NULL, SUM(sal + NVL(comm,0)) sal
FROM emp
GROUP BY job UNION ALL
SELECT NULL, NULL, SUM(sal + NVL(comm,0)) sal
FROM emp;
--group_ad2
SELECT DECODE(GROUPING(job),1,'총계',job) job,deptno, SUM(sal+NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP(job, deptno);
SELECT DECODE(GROUPING(job),1,'총',job) job,
NVL(DECODE(GROUPING(deptno),1,DECODE(GROUPING(job),1,'계'),deptno),'소계') sal,
SUM(sal+NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP(job, deptno);
--group ad2
SELECT CASE
WHEN GROUPING(job) = 1 THEN '총'
ELSE job
END job,
CASE
WHEN GROUPING(job)=1 THEN '계'
WHEN GROUPING(deptno) = 1 THEN '소계'
ELSE TO_CHAR(deptno)
END deptno,
SUM(sal+NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP(job, deptno);
--group ad3
SELECT deptno, job,SUM(sal+NVL(comm,0)) sal
FROM emp
GROUP BY ROLLUP(deptno,job);
--group ad4
SELECT dname, job,SUM(sal+NVL(comm,0)) sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP(dname,job)
ORDER BY dname, sal DESC;
--group ad5
SELECT NVL(dname,'총합') dname, job, SUM(sal+NVL(comm,0)) sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP(dname,job)
ORDER BY dname, sal DESC;
SELECT DECODE(GROUPING(dname),1,'총합',dname) dname, job, SUM(sal+NVL(comm,0)) sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY ROLLUP(dname,job)
ORDER BY dname, sal DESC;
--GROUPING SETS
SELECT job, deptno, SUM(sal +NVL(comm,0)) sal
FROM emp
GROUP BY GROUPING SETS(job, deptno,'');