当前位置: 代码迷 >> Oracle技术 >> 关于 group by 跟 over ( partition by .order by)
  详细解决方案

关于 group by 跟 over ( partition by .order by)

热度:474   发布时间:2016-04-24 08:24:41.0
关于 group by 和 over ( partition by ...order by)
如表a

id name date 其它列
1 a 2012-09-06 12:23:34 ...
2 a 2012-09-06 12:23:35 ...
3 b 2012-09-06 11:23:34 ...
4 b 2012-09-06 12:23:34 ...
5 c 2012-09-05 10:23:34 ...
6 c 2012-09-06 12:23:34 ...
7 c 2012-09-04 12:56:34 ...
8 d 2012-09-06 12:23:34 ...


要根据 name 分组 取出时间最大的记录

分别用 group by(最好没有子查询) 和 over()去实现

------解决方案--------------------
SQL code
--overselect a.* from a,(select row_number() over(partition by name order by "date" desc) rn,name,"date" from a) a1where a.name=a1.name and a."date"=a1."date" and a1.rn=1;--group by select * from a,(select name,max("date") d from a group by name) a1where a.name=a1.name and a."date"=a1.d;--maxselect * from a where a."date"=(select max("date") from a a1 where a.name=a1.name);
------解决方案--------------------
SQL code
select a.*  from test_1 a,       (select name, max(datetime) datetime from test_1 group by name) b where a.name = b.name   and a.datetime = b.datetime;select t2.*  from (select t1.*,               row_number() over(partition by t1.name order by t1.datetime desc nulls last) rn          from test_1 t1) t2 where rn = 1
------解决方案--------------------
oracle over()函数,从oracle 8i开始支持,后面的版本支持的比较好.
通常在做统计分析时我们都想尽可能多滴选择出原始列和统计值列,但是这样group by后面就必须跟随更多的列,使用分析函数可以避免使用group by时选择出来的列名必须出现在group by列表中的痛苦.
  
--1、over() 注(9i下over括号内必须有内容,不允许为空,本文所有示例均在10g下运行的)
--所有人的总工资
select a.empno, a.ename, sum(a.sal) over() total from emp a;
  
EMPNO ENAME TOTAL
7369 SMITH 29025
7499 ALLEN 29025
7521 WARD 29025
7566 JONES 29025
7654 MARTIN 29025
7698 BLAKE 29025
7782 CLARK 29025
7788 SCOTT 29025
7839 KING 29025
7844 TURNER 29025
7876 ADAMS 29025
7900 JAMES 29025
7902 FORD 29025
7934 MILLER 29025
  
--2、over(partition by ...) 分组统计
--统计部门的平均工资
select a.empno,
a.ename,
b.dname,
to_char(round(avg(a.sal) over(partition by b.dname), 2),'$999,999,999.99') dept_avg
from emp a, dept b
 where a.deptno = b.deptno;
  
EMPNO ENAME DNAME DEPT_AVG
7934 MILLER ACCOUNTING $2,916.67
7839 KING ACCOUNTING $2,916.67
7782 CLARK ACCOUNTING $2,916.67
7876 ADAMS RESEARCH $2,175.00
7902 FORD RESEARCH $2,175.00
7566 JONES RESEARCH $2,175.00
7369 SMITH RESEARCH $2,175.00
7788 SCOTT RESEARCH $2,175.00
7521 WARD SALES $1,566.67
7844 TURNER SALES $1,566.67
7499 ALLEN SALES $1,566.67
7900 JAMES SALES $1,566.67
7698 BLAKE SALES $1,566.67
7654 MARTIN SALES $1,566.67
  
--查询出管理员工人数最多的人的名字和他管理的人的名字
select b.ename, t.ename, t.mgr, t.cnt
from (select a.empno,
a.ename,
a.mgr,
count(1) over(partition by a.mgr) cnt
from emp a) t,
emp b
 where t.mgr = b.empno;
  
ENAME ENAME MGR CNT
JONES SCOTT 7566 2
JONES FORD 7566 2
BLAKE WARD 7698 5
BLAKE TURNER 7698 5
BLAKE ALLEN 7698 5
BLAKE JAMES 7698 5
BLAKE MARTIN 7698 5
  相关解决方案