关于 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()去实现
[解决办法]
--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);
[解决办法]
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
CLARK MILLER 7782 1
SCOTT ADAMS 7788 1
KING BLAKE 7839 3
KING JONES 7839 3
KING CLARK 7839 3
FORD SMITH 7902 1
--3、over(order by ...) 排序统计
select a.empno,
a.deptno,
a.ename,
a.sal,
sum(a.sal) over(order by a.ename) sum
from emp a;
EMPNO DEPTNO ENAME SAL SUM
7876 20 ADAMS 1100.00 1100
7499 30 ALLEN 1600.00 2700
7698 30 BLAKE 2850.00 5550
7782 10 CLARK 2450.00 8000
7902 20 FORD 3000.00 11000
7900 30 JAMES 950.00 11950
7566 20 JONES 2975.00 14925
7839 10 KING 5000.00 19925
7654 30 MARTIN 1250.00 21175
7934 10 MILLER 1300.00 22475
7788 20 SCOTT 3000.00 25475
7369 20 SMITH 800.00 26275
7844 30 TURNER 1500.00 27775
7521 30 WARD 1250.00 29025
--4、over(partition by ... order by ...) 分组排序统计
--统计各部门薪水前三名的人员
select t.*
from (select rank() over(partition by b.dname order by a.sal desc) rk,
a.empno,
a.ename,
b.dname,
a.sal
from emp a, dept b
where a.deptno = b.deptno) t
where t.rk <= 3;
RK EMPNO ENAME DNAME SAL
1 7839 KING ACCOUNTING 5000.00
2 7782 CLARK ACCOUNTING 2450.00
3 7934 MILLER ACCOUNTING 1300.00
1 7902 FORD RESEARCH 3000.00
1 7788 SCOTT RESEARCH 3000.00
3 7566 JONES RESEARCH 2975.00
1 7698 BLAKE SALES 2850.00
2 7499 ALLEN SALES 1600.00