达梦(DM) SQL聚集函数及日期运算操作

-- 查询每个部门员工的平均薪资、最小薪资、最大薪资、总工资及总记录SELECT deptno,         AVG (salary)AS 平均值,         MIN (salary)AS 最小值,         MAX (salary)AS 最大值,         SUM (salary)AS 工资合计,COUNT(*)AS 总行数
FROM employee GROUPBY deptno;

CREATETABLE employee2 ASSELECT*FROM employee WHERE1=2;

SELECTCOUNT(*)AS cnt, SUM (salary)AS sum_sal FROM employee2 WHERE deptno =11;

SELECTCOUNT(*)AS cnt, SUM (salary)AS sum_sal FROM employee2 WHERE deptno =11groupby deptno;

truncate table TEST

-- 按员工编号排序对员工的工资进行累加SELECT empno AS 编号,       ename AS 姓名,       salary AS 人工成本,       SUM (salary) OVER (ORDERBY empno)AS 成本累计
FROM employee
WHERE deptno =1;

-- 使用 listagg 函数模拟员工总成本的累加值SELECT empno                                                AS 编号,       ename                                              AS 姓名,       salary                                                     AS 人工成本,       SUM (salary) OVER (ORDERBY empno)AS 成本累计,(SELECT LISTAGG (salary,'+') WITHIN GROUP(ORDERBY empno)FROM employee b
WHERE b.deptno=1AND b.empno<= a.empno)  计算公式
FROM employee a
WHERE deptno =1ORDERBY empno;

CREATEOR REPLACE VIEW v (id,amt,trx)ASSELECT1,100,'PR'FROM dual UNION ALL
SELECT2,100,'PR'FROM dual UNION ALL
SELECT3,50,'PY'FROM dual UNION ALL
SELECT4,100,'PR'FROM dual UNION ALL
SELECT5,200,'PY'FROM dual UNION ALL
SELECT6,50,'PY'FROM dual;SELECT*FROM v;

SELECT id,         CASE WHEN trx ='PY' THEN '取款' ELSE '存款' END 存取类型,         amt 金额,(CASE WHEN trx ='PY' THEN -amt ELSE amt END)AS 余额
FROM v
ORDERBY id;

SELECT id,         CASE WHEN trx ='PY' THEN '取款' ELSE '存款' END 存取类型,         amt 金额,         SUM (CASE WHEN trx ='PY' THEN -amt ELSE amt END) OVER (ORDERBY id)AS 余额
FROM v
ORDERBY id;

SELECT salary,COUNT(*)AS 出现次数 FROM employee GROUPBY salary;

SELECT salary,DENSE_RANK () OVER (ORDERBY 出现次数 DESC)AS 次数排序 FROM(SELECT salary,COUNT(*)AS 出现次数 FROM employee GROUPBY salary);

SELECT salary FROM(SELECT salary, DENSE_RANK () OVER (ORDERBY 出现次数 DESC)AS 次数排序
FROM(SELECT salary,COUNT(*)AS 出现次数 FROM employee GROUPBY salary) x) y
WHERE 次数排序 =1;

SELECT deptno, salary FROM(SELECT deptno,salary,DENSE_RANK () OVER (PARTITION BY deptno ORDERBY 出现次数 DESC)AS 次数排序
FROM(SELECT salary, deptno,COUNT(*)AS 出现次数 FROM employee GROUPBY deptno, salary) x) y
WHERE 次数排序 =1;

SELECT hiredate AS 聘用日期,       add_days (hiredate,-5)AS 减5天,       add_days (hiredate,5)AS 加5天,       add_months (hiredate,-5)AS 减5月,       add_months (hiredate,5)AS 加5月,       add_months (hiredate,-5*12)AS 减5年,       add_months (hiredate,5*12)AS 加5年
FROM employee
WHERE ROWNUM <=1;

select sysdate as 当前日期,sysdate -5.0/24as 减5小时,sysdate +5.0/24as 加5小时,sysdate -5.0/24/60as 减5分钟,sysdate +5.0/24/60as 加5分钟
from dual;

SELECT 间隔天数,       间隔天数 *24AS 间隔小时,       间隔天数 *24*60AS 间隔分,       间隔天数 *24*60*60AS 间隔秒
FROM(SELECT MAX (hiredate)- MIN (hiredate)AS 间隔天数
FROM employee
WHERE empno IN(1,2)) x;

SELECT max_hd - min_hd 间隔天,       MONTHS_BETWEEN (max_hd, min_hd) 间隔月,       MONTHS_BETWEEN (max_hd, min_hd)/12 间隔年
FROM(SELECT MAX (hiredate) max_hd, MIN (hiredate) min_hd
FROM employee);

SELECT empno, hiredate FROM employee WHERE empno IN(1,2);

SELECT MIN (hiredate)AS min_hd, MAX (hiredate)AS max_hd FROM employee WHERE empno IN(1,2);

select(max_hd-min_hd)+1as 天数 from(SELECT MIN (hiredate)AS min_hd, MAX (hiredate)AS max_hd FROM employee WHERE empno IN(1,2));

CREATETABLE t500 ASSELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <=500;

SELECT min_hd +(t500.id-1)AS 日期
FROM(SELECT MIN (hiredate)AS min_hd, MAX (hiredate)AS max_hd FROM employee WHERE empno IN(1,2)) x,t500
WHERE t500.id<=((max_hd - min_hd)+1);

SELECT 日期, TO_CHAR (日期,'DY','NLS_DATE_LANGUAGE = American')AS dy FROM(SELECT min_hd +(t500.id-1)AS 日期
FROM(SELECT MIN (hiredate)AS min_hd, MAX (hiredate)AS max_hd FROM employee WHERE empno IN(1,2)) x,t500
WHERE t500.id<=((max_hd - min_hd)+1));

SELECTCOUNT(*)FROM(SELECT 日期, TO_CHAR (日期,'DY','NLS_DATE_LANGUAGE = American')AS dy FROM(SELECT min_hd +(t500.id-1)AS 日期
FROM(SELECT MIN (hiredate)AS min_hd, MAX (hiredate)AS max_hd FROM employee WHERE empno IN(1,2)) x,t500
WHERE t500.id<=((max_hd - min_hd)+1)))WHERE dy NOTIN('SAT','SUN');

WITH x0 AS(SELECT TO_DATE ('2023-01-01','yyyy-mm-dd')AS 年初 FROM DUAL),     x1 AS(SELECT 年初, ADD_MONTHS (年初,12)AS 下年初 FROM x0),     x2 AS(SELECT 年初, 下年初, 下年初 - 年初 AS 天数 FROM x1),     x3 AS(SELECT 年初 +(LEVEL -1)AS 日期 FROM x2 CONNECT BY LEVEL <= 天数),     x4 AS(SELECT 日期, TO_CHAR (日期,'DY')AS 星期 FROM x3)SELECT 星期,COUNT(*)AS 天数 FROM x4 GROUPBY 星期;

SELECT empno,ename,hiredate,LEAD (hiredate) OVER (ORDERBY hiredate) next_hd FROM employee;

SELECT empno,ename,hiredate,next_hd,next_hd-hiredate diff FROM(SELECT empno,ename,hiredate,LEAD (hiredate) OVER (ORDERBY hiredate) next_hd FROM employee);