postgresql sum max min

创建表

create table(empno smallint, ename varchar(20), job varchar(20), mgr smallint, hiredate date, sal bigint, comm bigint, deptno smallint);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALEMAN', 7698, '2014-11-12', 16000, 300, 30);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALEMAN', 7698, '2014-11-12', 16000, 300, 30);

insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALEMAN', 7698, '2016-09-12', 12000, 1400, 30);

select * from jinbo.employee;
 empno | ename  |   job   | mgr  |  hiredate  |  sal  | comm | deptno 
-------+--------+---------+------+------------+-------+------+--------
  7499 | ALLEN  | SALEMAN | 7698 | 2014-11-12 | 16000 |  300 |     30
  7566 | JONES  | MANAGER | 7839 | 2015-12-12 | 32000 |    0 |     20
  7654 | MARTIN | SALEMAN | 7698 | 2016-09-12 | 12000 | 1400 |     30
(3 rows)

查询所有员工信息

# select * from jinbo.employee;
 empno | ename  |   job   | mgr  |  hiredate  |  sal  | comm | deptno 
-------+--------+---------+------+------------+-------+------+--------
  7499 | ALLEN  | SALEMAN | 7698 | 2014-11-12 | 16000 |  300 |     30
  7566 | JONES  | MANAGER | 7839 | 2015-12-12 | 32000 |    0 |     20
  7654 | MARTIN | SALEMAN | 7698 | 2016-09-12 | 12000 | 1400 |     30
(3 rows)

查询每个部门的最高薪与最低薪

# select deptno, max(sal), min(sal) from jinbo.employee group by deptno;
 deptno |  max  |  min  
--------+-------+-------
     30 | 16000 | 12000
     20 | 32000 | 32000

查询每个部门的总薪资

# select deptno, sum(sal) from jinbo.employee  group by deptno;
 deptno |  sum  
--------+-------
     30 | 28000
     20 | 32000

查询 按从高到低顺序同组下一名的薪资

(不为拉仇恨,只为引斗志)

# select deptno, sal, lead(sal, 1) over w, lag(sal, 1) over w from jinbo.employee window w as (partition by deptno order by sal desc) order by deptno, sal desc;
 deptno |  sal  | lead  |  lag  
--------+-------+-------+-------
     20 | 32000 |       |      
     30 | 16000 | 12000 |      
     30 | 12000 |       | 16000
(3 rows)

#等同于

# select deptno, sal, lead(sal, 1) over (partition by deptno order by sal desc), lag(sal, 1) over (partition by deptno order by sal desc) from jinbo.employee order by deptno, sal desc;
 deptno |  sal  | lead  |  lag  
--------+-------+-------+-------
     20 | 32000 |       |      
     30 | 16000 | 12000 |      
     30 | 12000 |       | 16000
(3 rows)

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值