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

Question

如表aid  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()去实现

Answer 1

--over
select a.* from a,
(select row_number() over(partition by name order by "date" desc) rn,name,"date" from a) a1
where 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) a1
where a.name=a1.name and a."date"=a1.d;--max
select * from a where a."date"=(select max("date") from a a1 where a.name=a1.name);

Answer 2

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直接查询max最的第三种方式存在问题，如果相同的name，日期刚好相同，查询结果是错误的

Answer 3

你用的over()相当于没用，你还是把over结果作为了1个table去和原表的name，date进行了数据过滤，
和group by 的出的结果和原表对比有什么不同吗？

Answer 4

我要给每一组都添加序号怎么实现 每组的记录都给排序 不使用 rownum over()函数 只用group如表aid name date 其它列            rank
1 a 2012-09-06 12:23:34 ...   1
2 a 2012-09-06 12:23:35 ...   2
3 b 2012-09-06 11:23:34 ...   1
4 b 2012-09-06 12:23:34 ...   2
5 c 2012-09-05 10:23:34 ...   1
6 c 2012-09-06 12:23:34 ...   2
7 c 2012-09-04 12:56:34 ...   3
8 d 2012-09-06 12:23:34 ...   1
 

Answer 5

csdn人熊啊我提出的问题从来没有被解决过

Answer 6

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