oracle转mysql8.0
数据类型
oracle:varchar2
mysql: varchar
oracle: number
mysql:
if n<=3
then
tinyintif n<=5
then
smallintif n<=7
then
mediumintif n<=10
then
intif n<=20
then
bigint
else
decimal(n)
oracle: date
mysql: datetime
函数
oracle: sysdate()
mysql: now()
oracle: to_date(sysdate,'yy-mm-dd');
mysql: str_to_date
oracle: nvl(expression1,expression2)
if expression1 == null then
return expression2
elif expression1 != null then
return expression1
elif expression1 ==null and expression2== null then
return null
fi
mysql: ifnull(expression1,expression2)
oracle: months_between(date1,date2)
months_between:用于计算date1和date之间有几个月
mysql: DATEDIFF(date1,date2)
DATEDIFF: 用于计算date1和date之间有几天
oracle:add_month(d,n)
返回计算后相加的新日期。
d表示日期，n表示要加的月数
mysql:date_add(d,interval 1 day)
含义: 向日期添加指定的时间间隔.
date_add('日期',interval 1 day),在此日期的基础上增加一天;
窗口函数
[2021.5.14日更新]
- oracle准备数据:
CREATE TABLE dept (
deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR2(13)
);
CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
COMMIT;
- greatdb准备数据:
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13)
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE EMP
(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR DOUBLE,
HIREDATE DATE,
SAL DOUBLE,
COMM DOUBLE,
DEPTNO INT,
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
-
max() over() between
- 求工资最高的员工编号和部门编号
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2;
oracle_result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_COMP
---------- ---------- ---------- ----------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 5000
20 7566 2975 5000
20 7788 3000 5000
20 7876 1100 5000
20 7902 3000 5000
30 7499 1600 5000
30 7521 1250 5000
30 7654 1250 5000
DEPTNO EMPNO SAL MAX_COMP
---------- ---------- ---------- ----------
30 7698 2850 5000
30 7844 1500 5000
30 7900 950 5000
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over() max_comp
-> from emp e
-> order by 1,2;
+--------+-------+------+----------+
| deptno | empno | sal | max_comp |
+--------+-------+------+----------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 5000 |
| 20 | 7566 | 2975 | 5000 |
| 20 | 7788 | 3000 | 5000 |
| 20 | 7876 | 1100 | 5000 |
| 20 | 7902 | 3000 | 5000 |
| 30 | 7499 | 1600 | 5000 |
| 30 | 7521 | 1250 | 5000 |
| 30 | 7654 | 1250 | 5000 |
| 30 | 7698 | 2850 | 5000 |
| 30 | 7844 | 1500 | 5000 |
| 30 | 7900 | 950 | 5000 |
+--------+-------+------+----------+
14 rows in set (0.00 sec)
- 按部门分组求最大
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_DEP
---------- ---------- ---------- ----------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 2850
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL MAX_DEP
---------- ---------- ---------- ----------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(partition by e.deptno) max_dep
-> from emp e
-> order by 1,2;
+--------+-------+------+---------+
| deptno | empno | sal | max_dep |
+--------+-------+------+---------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 2850 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+---------+
14 rows in set (0.00 sec)
- 按照员工编号（empno）的排序取最大
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_ORDER_BY_EMP
---------- ---------- ---------- ----------------
10 7782 2450 2975
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 5000
20 7902 3000 5000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 2975
DEPTNO EMPNO SAL MAX_ORDER_BY_EMP
---------- ---------- ---------- ----------------
30 7698 2850 2975
30 7844 1500 5000
30 7900 950 5000
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(order by e.empno) max_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+------------------+
| deptno | empno | sal | max_order_by_emp |
+--------+-------+------+------------------+
| 10 | 7782 | 2450 | 2975 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 5000 |
| 20 | 7902 | 3000 | 5000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 2975 |
| 30 | 7698 | 2850 | 2975 |
| 30 | 7844 | 1500 | 5000 |
| 30 | 7900 | 950 | 5000 |
+--------+-------+------+------------------+
14 rows in set (0.00 sec)
- 按部门（deptno）分组，同时按员工编号（empno）排序取员工部门内最大
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
10 7782 2450 2450
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 1600
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+--------------------+
| deptno | empno | sal | p_dep_order_by_emp |
+--------+-------+------+--------------------+
| 10 | 7782 | 2450 | 2450 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 1600 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+--------------------+
14 rows in set (0.00 sec)
- 按范围部门总收入（between）,获取部门所有行
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_ALL
---------- ---------- ---------- -------------------------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 2850
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_ALL
---------- ---------- ---------- -------------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
-> from emp e
-> order by 1,2;
+--------+-------+------+---------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_all |
+--------+-------+------+---------------------------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 2850 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+---------------------------+
14 rows in set (0.01 sec)
- 按范围部门总收入（between）,获取第一行到当前行
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and current row) p_dep_order_by_emp_bw_1_curr
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and current row) p_dep_order_by_emp_bw_1_curr
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_1_CURR
---------- ---------- ---------- ----------------------------
10 7782 2450 2450
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
max over() between
max() over() betwee
求工资最高的员工编号和部门编号
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2;
oracle_result:

SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_COMP
---------- ---------- ---------- ----------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 5000
20 7566 2975 5000
20 7788 3000 5000
20 7876 1100 5000
20 7902 3000 5000
30 7499 1600 5000
30 7521 1250 5000
30 7654 1250 5000
DEPTNO EMPNO SAL MAX_COMP
---------- ---------- ---------- ----------
30 7698 2850 5000
30 7844 1500 5000
30 7900 950 5000
14 rows selected.
greatdb:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over() max_comp
from emp e
order by 1,2;
result:

GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over() max_comp
-> from emp e
-> order by 1,2;
+--------+-------+------+----------+
| deptno | empno | sal | max_comp |
+--------+-------+------+----------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 5000 |
| 20 | 7566 | 2975 | 5000 |
| 20 | 7788 | 3000 | 5000 |
| 20 | 7876 | 1100 | 5000 |
| 20 | 7902 | 3000 | 5000 |
| 30 | 7499 | 1600 | 5000 |
| 30 | 7521 | 1250 | 5000 |
| 30 | 7654 | 1250 | 5000 |
| 30 | 7698 | 2850 | 5000 |
| 30 | 7844 | 1500 | 5000 |
| 30 | 7900 | 950 | 5000 |
+--------+-------+------+----------+
14 rows in set (0.00 sec)
按部门分组求最大
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2;
result:

SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_DEP
---------- ---------- ---------- ----------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 2850
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL MAX_DEP
---------- ---------- ---------- ----------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno) max_dep
from emp e
order by 1,2;
result:

GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(partition by e.deptno) max_dep
-> from emp e
-> order by 1,2;
+--------+-------+------+---------+
| deptno | empno | sal | max_dep |
+--------+-------+------+---------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 2850 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+---------+
14 rows in set (0.00 sec)
按照员工编号（empno）的排序取最大
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2;
result:

SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL MAX_ORDER_BY_EMP
---------- ---------- ---------- ----------------
10 7782 2450 2975
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 5000
20 7902 3000 5000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 2975
DEPTNO EMPNO SAL MAX_ORDER_BY_EMP
---------- ---------- ---------- ----------------
30 7698 2850 2975
30 7844 1500 5000
30 7900 950 5000
14 rows selected.
greatdb:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(order by e.empno) max_order_by_emp
from emp e
order by 1,2;
result:

GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(order by e.empno) max_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+------------------+
| deptno | empno | sal | max_order_by_emp |
+--------+-------+------+------------------+
| 10 | 7782 | 2450 | 2975 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 5000 |
| 20 | 7902 | 3000 | 5000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 2975 |
| 30 | 7698 | 2850 | 2975 |
| 30 | 7844 | 1500 | 5000 |
| 30 | 7900 | 950 | 5000 |
+--------+-------+------+------------------+
14 rows in set (0.00 sec)
按部门（deptno）分组，同时按员工编号（empno）排序取员工部门内最大
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:

SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
10 7782 2450 2450
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 1600
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:

select e.deptno,
e.empno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:

GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+--------------------+
| deptno | empno | sal | p_dep_order_by_emp |
+--------+-------+------+--------------------+
| 10 | 7782 | 2450 | 2450 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 1600 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+--------------------+
14 rows in set (0.00 sec)
按范围部门总收入（between）,获取部门所有行
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2;
result:

SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_ALL
---------- ---------- ---------- -------------------------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 2850
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_ALL
---------- ---------- ---------- -------------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:

select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
from emp e
order by 1,2;
result:

GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) p_dep_order_by_emp_bw_all
-> from emp e
-> order by 1,2;
+--------+-------+------+---------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_all |
+--------+-------+------+---------------------------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 2850 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+---------------------------+
14 rows in set (0.01 sec)
按范围部门总收入（between）,获取第一行到当前行
oracle:

select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and current row) p_dep_order_by_emp_bw_1_curr
from emp e
order by 1,2;
result:
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 1600
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_1_CURR
---------- ---------- ---------- ----------------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 2850
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and current row) p_dep_order_by_emp_bw_1_curr
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and current row) p_dep_order_by_emp_bw_1_curr
-> from emp e
-> order by 1,2;
+--------+-------+------+------------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_1_curr |
+--------+-------+------+------------------------------+
| 10 | 7782 | 2450 | 2450 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 1600 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 2850 |
+--------+-------+------+------------------------------+
14 rows in set (0.00 sec)
- 按范围部门总收入（between）,获取当前行到最后行
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between current row and unbounded following) p_dep_order_by_emp_bw_curr_end
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between current row and unbounded following) p_dep_order_by_emp_bw_curr_end
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_CURR_END
---------- ---------- ---------- ------------------------------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 1300
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 2850
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_CURR_END
---------- ---------- ---------- ------------------------------
30 7698 2850 2850
30 7844 1500 1500
30 7900 950 950
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between current row and unbounded following) p_dep_order_by_emp_bw_curr_end
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between current row and unbounded following) p_dep_order_by_emp_bw_curr_end
-> from emp e
-> order by 1,2;
+--------+-------+------+--------------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_curr_end |
+--------+-------+------+--------------------------------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 1300 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 2850 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 1500 |
| 30 | 7900 | 950 | 950 |
+--------+-------+------+--------------------------------+
14 rows in set (0.00 sec)
- 按范围部门总收入（between）,获取当前行上1行到当前行
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and current row) p_dep_order_by_emp_bw_pri1_cur
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and current row) p_dep_order_by_emp_bw_pri1_cur
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_CUR
---------- ---------- ---------- ------------------------------
10 7782 2450 2450
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 1600
30 7521 1250 1600
30 7654 1250 1250
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_CUR
---------- ---------- ---------- ------------------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 1500
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and current row) p_dep_order_by_emp_bw_pri1_cur
from emp e
order by 1,2;
reslult:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and current row) p_dep_order_by_emp_bw_pri1_cur
-> from emp e
-> order by 1,2;
+--------+-------+------+--------------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_pri1_cur |
+--------+-------+------+--------------------------------+
| 10 | 7782 | 2450 | 2450 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 2975 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 1600 |
| 30 | 7654 | 1250 | 1250 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 1500 |
+--------+-------+------+--------------------------------+
14 rows in set (0.00 sec)
- 按范围部门总收入（between）,获取前行的上一行(rownum-1)到当前行的下两行(rownum+2)
oracle:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_F2
---------- ---------- ---------- -----------------------------
10 7782 2450 5000
10 7839 5000 5000
10 7934 1300 5000
20 7369 800 3000
20 7566 2975 3000
20 7788 3000 3000
20 7876 1100 3000
20 7902 3000 3000
30 7499 1600 1600
30 7521 1250 2850
30 7654 1250 2850
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_F2
---------- ---------- ---------- -----------------------------
30 7698 2850 2850
30 7844 1500 2850
30 7900 950 1500
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> max(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
-> from emp e
-> order by 1,2;
+--------+-------+------+-------------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_pri1_f2 |
+--------+-------+------+-------------------------------+
| 10 | 7782 | 2450 | 5000 |
| 10 | 7839 | 5000 | 5000 |
| 10 | 7934 | 1300 | 5000 |
| 20 | 7369 | 800 | 3000 |
| 20 | 7566 | 2975 | 3000 |
| 20 | 7788 | 3000 | 3000 |
| 20 | 7876 | 1100 | 3000 |
| 20 | 7902 | 3000 | 3000 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 2850 |
| 30 | 7698 | 2850 | 2850 |
| 30 | 7844 | 1500 | 2850 |
| 30 | 7900 | 950 | 1500 |
+--------+-------+------+-------------------------------+
14 rows in set (0.00 sec)
-
rank over()
-
获取全公司薪资rank
oracle:
select deptno,
empno,
sal,
rank() over(order by sal desc) r_order_sal_desc
from emp;
result:
SQL> select deptno,
empno,
sal,
rank() over(order by sal desc) r_order_sal_desc
from emp; 2 3 4 5
DEPTNO EMPNO SAL R_ORDER_SAL_DESC
---------- ---------- ---------- ----------------
10 7839 5000 1
20 7902 3000 2
20 7788 3000 2
20 7566 2975 4
30 7698 2850 5
10 7782 2450 6
30 7499 1600 7
30 7844 1500 8
10 7934 1300 9
30 7521 1250 10
30 7654 1250 10
DEPTNO EMPNO SAL R_ORDER_SAL_DESC
---------- ---------- ---------- ----------------
20 7876 1100 12
30 7900 950 13
20 7369 800 14
14 rows selected.
greatdb:
select deptno,
empno,
sal,
rank() over(order by sal desc) r_order_sal_desc
from emp;
result:
GreatDB Cluster[TEST]> select deptno,
-> empno,
-> sal,
-> rank() over(order by sal desc) r_order_sal_desc
-> from emp;
+--------+-------+------+------------------+
| deptno | empno | sal | r_order_sal_desc |
+--------+-------+------+------------------+
| 10 | 7839 | 5000 | 1 |
| 20 | 7788 | 3000 | 2 |
| 20 | 7902 | 3000 | 2 |
| 20 | 7566 | 2975 | 4 |
| 30 | 7698 | 2850 | 5 |
| 10 | 7782 | 2450 | 6 |
| 30 | 7499 | 1600 | 7 |
| 30 | 7844 | 1500 | 8 |
| 10 | 7934 | 1300 | 9 |
| 30 | 7521 | 1250 | 10 |
| 30 | 7654 | 1250 | 10 |
| 20 | 7876 | 1100 | 12 |
| 30 | 7900 | 950 | 13 |
| 20 | 7369 | 800 | 14 |
+--------+-------+------+------------------+
14 rows in set (0.00 sec)
- 按部门分组薪资rank
oracle:
select deptno,
empno,
sal,
rank() over(partition by deptno order by sal desc) p_dep_order_sal_desc
from emp
order by 1;
result:
SQL> select deptno,
empno,
sal,
rank() over(partition by deptno order by sal desc) p_dep_order_sal_desc
from emp
order by 1; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_SAL_DESC
---------- ---------- ---------- --------------------
10 7839 5000 1
10 7782 2450 2
10 7934 1300 3
20 7788 3000 1
20 7902 3000 1
20 7566 2975 3
20 7876 1100 4
20 7369 800 5
30 7698 2850 1
30 7499 1600 2
30 7844 1500 3
DEPTNO EMPNO SAL P_DEP_ORDER_SAL_DESC
---------- ---------- ---------- --------------------
30 7654 1250 4
30 7521 1250 4
30 7900 950 6
14 rows selected.
greatdb:
select deptno,
empno,
sal,
rank() over(partition by deptno order by sal desc) p_dep_order_sal_desc
from emp
order by 1;
result:
GreatDB Cluster[TEST]> select deptno,
-> empno,
-> sal,
-> rank() over(partition by deptno order by sal desc) p_dep_order_sal_desc
-> from emp
-> order by 1;
+--------+-------+------+----------------------+
| deptno | empno | sal | p_dep_order_sal_desc |
+--------+-------+------+----------------------+
| 10 | 7839 | 5000 | 1 |
| 10 | 7782 | 2450 | 2 |
| 10 | 7934 | 1300 | 3 |
| 20 | 7788 | 3000 | 1 |
| 20 | 7902 | 3000 | 1 |
| 20 | 7566 | 2975 | 3 |
| 20 | 7876 | 1100 | 4 |
| 20 | 7369 | 800 | 5 |
| 30 | 7698 | 2850 | 1 |
| 30 | 7499 | 1600 | 2 |
| 30 | 7844 | 1500 | 3 |
| 30 | 7521 | 1250 | 4 |
| 30 | 7654 | 1250 | 4 |
| 30 | 7900 | 950 | 6 |
+--------+-------+------+----------------------+
14 rows in set (0.00 sec)
-
sum over()
-
按部门分组求最和
oracle:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno) sum_dep
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno) sum_dep
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL SUM_DEP
---------- ---------- ---------- ----------
10 7782 2450 8750
10 7839 5000 8750
10 7934 1300 8750
20 7369 800 10875
20 7566 2975 10875
20 7788 3000 10875
20 7876 1100 10875
20 7902 3000 10875
30 7499 1600 9400
30 7521 1250 9400
30 7654 1250 9400
DEPTNO EMPNO SAL SUM_DEP
---------- ---------- ---------- ----------
30 7698 2850 9400
30 7844 1500 9400
30 7900 950 9400
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno) sum_dep
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> sum(e.sal) over(partition by e.deptno) sum_dep
-> from emp e
-> order by 1,2;
+--------+-------+------+---------+
| deptno | empno | sal | sum_dep |
+--------+-------+------+---------+
| 10 | 7782 | 2450 | 8750 |
| 10 | 7839 | 5000 | 8750 |
| 10 | 7934 | 1300 | 8750 |
| 20 | 7369 | 800 | 10875 |
| 20 | 7566 | 2975 | 10875 |
| 20 | 7788 | 3000 | 10875 |
| 20 | 7876 | 1100 | 10875 |
| 20 | 7902 | 3000 | 10875 |
| 30 | 7499 | 1600 | 9400 |
| 30 | 7521 | 1250 | 9400 |
| 30 | 7654 | 1250 | 9400 |
| 30 | 7698 | 2850 | 9400 |
| 30 | 7844 | 1500 | 9400 |
| 30 | 7900 | 950 | 9400 |
+--------+-------+------+---------+
14 rows in set (0.00 sec)
- 按照员工编号（empno）的排序取最和
oracle:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(order by e.empno) sum_order_by_emp
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(order by e.empno) sum_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL SUM_ORDER_BY_EMP
---------- ---------- ---------- ----------------
10 7782 2450 13175
10 7839 5000 21175
10 7934 1300 29025
20 7369 800 800
20 7566 2975 6625
20 7788 3000 16175
20 7876 1100 23775
20 7902 3000 27725
30 7499 1600 2400
30 7521 1250 3650
30 7654 1250 7875
DEPTNO EMPNO SAL SUM_ORDER_BY_EMP
---------- ---------- ---------- ----------------
30 7698 2850 10725
30 7844 1500 22675
30 7900 950 24725
14 rows selected.
geatdb:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(order by e.empno) sum_order_by_emp
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> sum(e.sal) over(order by e.empno) sum_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+------------------+
| deptno | empno | sal | sum_order_by_emp |
+--------+-------+------+------------------+
| 10 | 7782 | 2450 | 13175 |
| 10 | 7839 | 5000 | 21175 |
| 10 | 7934 | 1300 | 29025 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 6625 |
| 20 | 7788 | 3000 | 16175 |
| 20 | 7876 | 1100 | 23775 |
| 20 | 7902 | 3000 | 27725 |
| 30 | 7499 | 1600 | 2400 |
| 30 | 7521 | 1250 | 3650 |
| 30 | 7654 | 1250 | 7875 |
| 30 | 7698 | 2850 | 10725 |
| 30 | 7844 | 1500 | 22675 |
| 30 | 7900 | 950 | 24725 |
+--------+-------+------+------------------+
14 rows in set (0.01 sec)
- 按部门（deptno）分组，同时按员工编号（empno）排序取员工部门内求和
oracle:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
10 7782 2450 2450
10 7839 5000 7450
10 7934 1300 8750
20 7369 800 800
20 7566 2975 3775
20 7788 3000 6775
20 7876 1100 7875
20 7902 3000 10875
30 7499 1600 1600
30 7521 1250 2850
30 7654 1250 4100
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP
---------- ---------- ---------- ------------------
30 7698 2850 6950
30 7844 1500 8450
30 7900 950 9400
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
sum(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> sum(e.sal) over(partition by e.deptno order by e.empno) p_dep_order_by_emp
-> from emp e
-> order by 1,2;
+--------+-------+------+--------------------+
| deptno | empno | sal | p_dep_order_by_emp |
+--------+-------+------+--------------------+
| 10 | 7782 | 2450 | 2450 |
| 10 | 7839 | 5000 | 7450 |
| 10 | 7934 | 1300 | 8750 |
| 20 | 7369 | 800 | 800 |
| 20 | 7566 | 2975 | 3775 |
| 20 | 7788 | 3000 | 6775 |
| 20 | 7876 | 1100 | 7875 |
| 20 | 7902 | 3000 | 10875 |
| 30 | 7499 | 1600 | 1600 |
| 30 | 7521 | 1250 | 2850 |
| 30 | 7654 | 1250 | 4100 |
| 30 | 7698 | 2850 | 6950 |
| 30 | 7844 | 1500 | 8450 |
| 30 | 7900 | 950 | 9400 |
+--------+-------+------+--------------------+
14 rows in set (0.00 sec)
- 前行的上一行(rownum-1)到当前行的下两行(rownum+2)
oracle:
select e.deptno,
e.empno,
e.sal,
sum(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2;
result:
SQL> select e.deptno,
e.empno,
e.sal,
sum(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2; 2 3 4 5 6
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_F2
---------- ---------- ---------- -----------------------------
10 7782 2450 8750
10 7839 5000 8750
10 7934 1300 6300
20 7369 800 6775
20 7566 2975 7875
20 7788 3000 10075
20 7876 1100 7100
20 7902 3000 4100
30 7499 1600 4100
30 7521 1250 6950
30 7654 1250 6850
DEPTNO EMPNO SAL P_DEP_ORDER_BY_EMP_BW_PRI1_F2
---------- ---------- ---------- -----------------------------
30 7698 2850 6550
30 7844 1500 5300
30 7900 950 2450
14 rows selected.
greatdb:
select e.deptno,
e.empno,
e.sal,
sum(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
from emp e
order by 1,2;
result:
GreatDB Cluster[TEST]> select e.deptno,
-> e.empno,
-> e.sal,
-> sum(e.sal)over(partition by e.deptno order by e.empno rows between 1 preceding and 2 following) p_dep_order_by_emp_bw_pri1_f2
-> from emp e
-> order by 1,2;
+--------+-------+------+-------------------------------+
| deptno | empno | sal | p_dep_order_by_emp_bw_pri1_f2 |
+--------+-------+------+-------------------------------+
| 10 | 7782 | 2450 | 8750 |
| 10 | 7839 | 5000 | 8750 |
| 10 | 7934 | 1300 | 6300 |
| 20 | 7369 | 800 | 6775 |
| 20 | 7566 | 2975 | 7875 |
| 20 | 7788 | 3000 | 10075 |
| 20 | 7876 | 1100 | 7100 |
| 20 | 7902 | 3000 | 4100 |
| 30 | 7499 | 1600 | 4100 |
| 30 | 7521 | 1250 | 6950 |
| 30 | 7654 | 1250 | 6850 |
| 30 | 7698 | 2850 | 6550 |
| 30 | 7844 | 1500 | 5300 |
| 30 | 7900 | 950 | 2450 |
+--------+-------+------+-------------------------------+
14 rows in set (0.00 sec)
first_value over()
- 按empno顺序first_value
oracle:
select deptno,
empno,
ename,
first_value(ename) over(order by empno) first_ename_order_eno
from emp;
result:
SQL> select deptno,
empno,
ename,
first_value(ename) over(order by empno) first_ename_order_eno
from emp; 2 3 4 5
DEPTNO EMPNO ENAME FIRST_ENAM
---------- ---------- ---------- ----------
20 7369 SMITH SMITH
30 7499 ALLEN SMITH
30 7521 WARD SMITH
20 7566 JONES SMITH
30 7654 MARTIN SMITH
30 7698 BLAKE SMITH
10 7782 CLARK SMITH
20 7788 SCOTT SMITH
10 7839 KING SMITH
30 7844 TURNER SMITH
20 7876 ADAMS SMITH
DEPTNO EMPNO ENAME FIRST_ENAM
---------- ---------- ---------- ----------
30 7900 JAMES SMITH
20 7902 FORD SMITH
10 7934 MILLER SMITH
14 rows selected.
greatdb:
select deptno,
empno,
ename,
first_value(ename) over(order by empno) first_ename_order_eno
from emp;
result:
GreatDB Cluster[TEST]> select deptno,
-> empno,
-> ename,
-> first_value(ename) over(order by empno) first_ename_order_eno
-> from emp;
+--------+-------+--------+-----------------------+
| deptno | empno | ename | first_ename_order_eno |
+--------+-------+--------+-----------------------+
| 20 | 7369 | SMITH | SMITH |
| 30 | 7499 | ALLEN | SMITH |
| 30 | 7521 | WARD | SMITH |
| 20 | 7566 | JONES | SMITH |
| 30 | 7654 | MARTIN | SMITH |
| 30 | 7698 | BLAKE | SMITH |
| 10 | 7782 | CLARK | SMITH |
| 20 | 7788 | SCOTT | SMITH |
| 10 | 7839 | KING | SMITH |
| 30 | 7844 | TURNER | SMITH |
| 20 | 7876 | ADAMS | SMITH |
| 30 | 7900 | JAMES | SMITH |
| 20 | 7902 | FORD | SMITH |
| 10 | 7934 | MILLER | SMITH |
+--------+-------+--------+-----------------------+
14 rows in set (0.01 sec)
- 部门组内、递增数据first_value
oracle:
select deptno,
empno,
first_value(sal) over(partition by deptno order by empno) first_sal_dep_order_eno
from emp;
result:
SQL> select deptno,
empno,
first_value(sal) over(partition by deptno order by empno) first_sal_dep_order_eno
from emp; 2 3 4
DEPTNO EMPNO FIRST_SAL_DEP_ORDER_ENO
---------- ---------- -----------------------
10 7782 2450
10 7839 2450
10 7934 2450
20 7369 800
20 7566 800
20 7788 800
20 7876 800
20 7902 800
30 7499 1600
30 7521 1600
30 7654 1600
DEPTNO EMPNO FIRST_SAL_DEP_ORDER_ENO
---------- ---------- -----------------------
30 7698 1600
30 7844 1600
30 7900 1600
14 rows selected.
greatdb:
select deptno,
empno,
first_value(sal) over(partition by deptno order by empno) first_sal_dep_order_eno
from emp;
result:
GreatDB Cluster[TEST]> select deptno,
-> empno,
-> first_value(sal) over(partition by deptno order by empno) first_sal_dep_order_eno
-> from emp;
+--------+-------+-------------------------+
| deptno | empno | first_sal_dep_order_eno |
+--------+-------+-------------------------+
| 10 | 7782 | 2450 |
| 10 | 7839 | 2450 |
| 10 | 7934 | 2450 |
| 20 | 7369 | 800 |
| 20 | 7566 | 800 |
| 20 | 7788 | 800 |
| 20 | 7876 | 800 |
| 20 | 7902 | 800 |
| 30 | 7499 | 1600 |
| 30 | 7521 | 1600 |
| 30 | 7654 | 1600 |
| 30 | 7698 | 1600 |
| 30 | 7844 | 1600 |
| 30 | 7900 | 1600 |
+--------+-------+-------------------------+
14 rows in set (0.00 sec)
循环
oracle: for loop ... end loop
delimiter /
begin
for i in 1..10000 loop
insert into ora_alter_table(pid,did,lsn,name,pno,cnt,amount,sale_time) values(i,i+100,i+9,"name"i,i+1000,i+10,i+10000,sysdate());
end loop;
commit;
end;
/
mysql: LOOP.. end LOOP
create procedure pro_test1()
begin
declare i int;
set i = 1;
lp : LOOP
insert into ora_alter_table(pid,did,lsn,name,pno,cnt,amount,sale_time) values(i,i+100,i+9,"name"+i,i+1000,i+10,i+10000,NOW());
set i = i+1;
if i>=10000 then
leave lp;
end if;
end LOOP;
end
/
DDL语句
oracle: 修改默认值
alter table ora_alter_table modify pid default 0;
mysql:
如果有默认值,则先删除
alter table xxx alter column drop default;
如果没有默认值
alter table xxx alter column set default xx;
分区表
oracle: partition pdefault values in (default)
mysql: 不支持
oracle: subpartition p11 values (11)
mysql : (list分区)subpartition p11 values in (11)
@2021.9.13日更新
oracle中查看当前用户下的标识符名称
Select object_name From user_objects Where object_type='TRIGGER'; --所有触发器
Select object_name From user_objects Where object_type='PROCEDURE'; --所有存储过程
Select object_name From user_objects Where object_type='VIEW'; --所有视图
Select object_name From user_objects Where object_type='TABLE'; --所有表
select object_name from user_objects where object_type='FUNCTION';
select object_name from user_objects where object_type='PROCEDURE';
oracle将结果输出到文件:
spool on
set heading off
spool $HOME/yltest_function.txt
select object_name from user_objects where object_type='FUNCTION';
spool off
mysql查看数据库中所有标识符名字
select table_NAME from information_schema.views where table_SCHEMA=‘zjtest’;
show create procedure where db=‘test’;
show create function where db=‘test’;
3962
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
