sql练习

1.根据员工在各自部门中的高低排出在部门的名次

select rank() over( partition by deptno order by sal desc) rank,ename,sal,deptno from emp

2.查找平均工资最高的部门

select avg_sal,deptno    
from (select avg(sal) avg_sal,deptno from emp group by deptno) 
where avg_sal=(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno))

3. 求出列中相隔日期的天数

select ename,hiredate,lag(hiredate) over(order by hiredate) last_day, hiredate-(lag(hiredate)  over(order by hiredate)) lz_day  from emp

效果：

4.当要统计分组中的总合计时，ORACLE提供一个ROLLUP ,实现分组中的总合计

SELECT deptno,SUM(sal) 
FROM emp
GROUP BY ROLLUP(deptno)

5.从上往下查找第一个组织类型为部门的组织

SELECT ps.stru_tree_code,ps.org_alias,po.org_type_code
FROM pt_structure ps
JOIN pt_organzation po on po.org_id=ps.org_id
JOIN (select stru_tree_code from pt_structure ps1 join pt_organzation po on po.org_id=ps1.org_id where po.org_type_code in('1','105')) a
ON ps.stru_tree_code regexp concat(a.stru_tree_code,'-*[0-9]*$') 
WHERE po.org_type_code='4' AND ps.in_use='1'
ORDER BY ps.global_order