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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!