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

--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