用组函数统计数据 |
您所在的位置:网站首页 › excel统计范围内数据个数 › 用组函数统计数据 |
下面这篇文章带大家了解用组函数统计数据。 组函数有: AVG,COUNT,MAX,MIN,STDDEV,SUM,VARIANCE 分组查询语法: SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; 学习内容: select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%rep%';上面的结果是输出job_id含rep的职员的平均工资,最大工资,最小工资,总工资,即四列 select min(hire_date),max(hire_date) from employees;上面的结果是输出全部职员的最小招聘日期,最大招聘日期,两列 select count(*) from employees where department_id=50;上面的结果是输出department_id为50的职员的数量 select count(commission_pct) from employees where department_id=80;上面的结果是输出department_id为80的职员中commission_pct不为0的职员的数量 select count(distinct department_id) from employees;上面的结果是输出不同的department_id的数量 select department_id,avg(salary) from employees group by department_id;上面的结果是输出按照department_id分组后输出不同department_id的平均工资,有两列 select avg(salary) from employees group by department_id;上面的结果是输出按照department_id分组后输出不同department_id的平均工资,有一列 select department_id dept_id,job_id,sum(salary) from employees group by department_id,job_id;上面的结果是输出按照department_id和job_id分组后输出department_id,job_id,平均工资,其中department_id的别名设为dept_id select department_id,max(salary) from employees group by department_id having max(salary)>10000;上面的结果是输出按照最大工资大于10000的职员的department_id分组后最大工资 其中group by里有条件不用where而是用having select job_id,sum(salary) PAYROLL from employees where job_id not like '%rep%' group by job_id having sum(salary)>13000 order by sum(salary);上面的结果是输出按照总工资大于13000的job_id分组后选择job_id不含rep的职员的job_id,总工资别名为PAYROLL,按照总工资升序来排列 练习内容:1. 组函数在多行上计算,对每个组产生一个结果。 (判断题) True 2. 组函数在计算中包含空值。 (判断题) False 3. 在分组计算中,WHERE 子句对行的限制在计算的前面。 (判断题) True 4. 显示所有雇员的最高、最低、合计和平均薪水,列标签分别为:Maximum、Minimum、Sum 和 Average。四舍五入结果为最近的整数,将语句存在文本文件 lab5_4.sql 中。 (TIPS:四舍五入使用函数ROUND(number,0),涉及的表为employees。) 这里省去了0 select MAX(salary) Maximum,MIN(salary) Minimum,ROUND(SUM(salary)) Sum,ROUND(AVG(salary)) Average from employees;5. 修改 lab5_4.sql 中的问题显示每种工作类型的最低、最高、合计和平均薪水。再保存 lab5_4.sql 到 lab5_5.sql。运行 lab5_5.sql 中的语句。 (TIPS:使用GROUP BY) select job_id JOB_ID,MAX(salary) Maximum,MIN(salary) Minimum,ROUND(SUM(salary) Sum,ROUND(AVG(salary)) Average from employees group by job_id;6. 写一个查询显示每一工作岗位的人数。 (TIPS:使用GROUP BY,涉及的表为employees。) select job_id JOB_ID,COUNT(*) from employees group by job_id;7. 确定经理人数,不需要列出他们,列标签是 Number of Managers。 提示:用 MANAGER_ID 列决定经理号。(TIPS:注意去重。) select COUNT(DISTINCT manager_id) as "Number of Managers" from employees;8. 写一个查询显示最高和最低薪水之间的差。列标签是 DIFFERENCE。 select (MAX(salary)-MIN(salary)) DIFFERENCE from employees;9. 显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。 (TIPS:经理未知的记为null。) select manager_id,MIN(salary) from employees where manager_id is not NULL group by manager_id having MIN(salary)>6000 order by MIN(salary) desc;10. 写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。 (TIPS:涉及的表为employees和departments,四舍五入薪水到两位小数使用函数ROUND(number,2) 。) select departments.department_name as "Name",departments.location_id Location,COUNT(*) as "Number of People",ROUND(AVG(salary),2) Salary from employees,departments where departments.department_id=employees.department_id group by departments.department_name,departments.location_id;以上就是组函数部分的内容。如果有错误欢迎指出^_^ |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |