SQLserver 数据库创建分组和使用汇总函数 CUBE 和ROLLUP

下面的SQL语句纯属SQL语句，注释比较少，希望大家能明白只是做个简单的笔记

  group by:   select  emp_id,city   from employee_tbl  group by  city,emp_id;对返回数据city，在根据emp_id进行分组

select  emp_id , sum(salary) from employee_pay_tbl  group  by  salary, emp_id  返回 emp_id    salary字段的总和，然后根据薪水和雇员ID对数据分组

select   sum(salary)  as  tottal_salary   from  employee_pay_tbl;  返回全部薪水的总和

select  sum(salary)  from employee_pay_tbl   group  by  salary; 返回不同的薪水总和

select city ,count(*) from employee_tbl  group by city ;不同城市的记录总数

select city , avg(pay_rate),avg(salary) from employee_pay_tbl   group by city; 获得不同城市的平均小时工资和薪水

select city ,avg(pay_rate),avg(salary) from emp_pay_tmp where city in ('BEIJING','SHANGHAI') group by city  order by 2,3返回BEIJING SHANGHAI城市的平均小时和薪水，基于city进行分组 最后排序

 select city , max(pay_rate) ,min (salary) from emp_pay_tmp group by city;

CUBE 和ROLLUP

rollup :  group by rollup(order column list of grouping sets)

goup by order column list of grouping sets with rollp  (mysql中)

双表查询

select city ,zip ,avg(pay_rate),avg(salary)  from employee_tbl e inner join employee_pay_tbl   p on e.emp_id=p,emp_id group by city , zip order by  city , zip; 根据城市和邮编获得平均工资

select city zip,avg(pay_rate), avg(salary) from employee_tbl   e  inner join employee_pay_tbl   p on e.emp_id =p.emp_id group by rollup(city,zip);使用rollup语句获取小计数据

CUBE ： select city ,zip , avg(pay_rate) ,avg(salary) from employee_tbl   e inner join employee_pay_tbl   p on e.emp_id = p.emp_id group by cube(city,zip);根据每种字段组合分别汇总

having: 位置 

select 

from 

where 

group by 

having

order by

select city ,avg(pay_rate),avg(salary) from emp_pay_tmp where city <> 'GREENWOOD' group by city having  avg(salary) >2000 order by 3;选择除了GREENWOOD之外的所有城市的平均小时工资和薪水，输出按照 分组， 显示超过2000的分组（城市）