关于分组(group by;having,over partition by)语法的基本应用

over partition by与group by 的区别 group by 只能得到分组后的统计数据，over partition by 不仅可以得到分组后的统计数据，还可以同时显示明细数据。 group by 是在where子句之后；over partition by 是from子句之前。

 

下面是关于分组(group by;having)语法的基本应用:

其基本的执行顺序是:

    --where
    --group by
    --分组函数
    --having 后的表达式必须是分组表达式（只与分组函数有关，与表的单行单列无关）
    --order by

所以如果需要写出高性能的sql语句;需要:

    --尽早的在where中，选择需要的数据，
    --然后再在选择的数据中进行操作。。

 

select veh.workrange1 AS workRange1,count(1) AS count_workRange1,round(avg(veh.amount),2)
from tbl_veh_baseinfo veh
where veh.amount > (select avg(amount) from tbl_veh_baseinfo)
group by veh.workrange1
having avg(veh.amount) > (select avg(amount) from tbl_veh_baseinfo) And count(1) > 200
order by VEH.workRange1 desc

 

over partition by与group by 的区别:　最主要over partition by 除了得到汇总信息外还可以得到记录的明细,而group by 只能得到汇总信息.

比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000 SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM DUAL UNION ALL
SELECT 'E',10,1000 FROM DUAL;
NAME DEPT SALARY
A 10 1000
B 10 2000
C 20 1500
D 20 3000
E 10 1000
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：
select name,dept,salary,sum(salary) over (partition by dept) total_salary from salary;
name dept salary tatal_salary
A 10 1000 4000
B 10 2000 4000
E 10 1000 4000
C 20 1500 4500
D 20 3000 4500

用goup by 就没办法做到这点，只能查询到每个部门的总工资：
select dept,sum(salary) total_salary from salary group by dept
dept total_salary
10 4000
20 4500
另外over partion by 还可以做到查询每位员工占部门总工资的百分比：
select name,dept,salary,salary*100/sum(salary) over (partition by dept) percent from salary;

name dept salary percent
A 10 1000 25
B 10 2000 50
E 10 1000 25
C 20 1500 33.3333333333333
D 20 3000 66.6666666666667