还是person表和dept表(跟之前用的一样)
1. 其他查询
1.1 临时表查询
题目1:查询高于本部门平均工资的人员:
select * from person as p1,(select dept_id,avg(salary)as salary from person group by dept_id) as p2 where p1.dept_id=p2.dept_id and p1.salary>p2.salary;
运行结果:
其实看题也应该知道是需要用到两张表,因为需要显示高于部门平均工资的所有人员信息,需要用到person表,另外一张就是基于dept_id进行分组的平均工资表;
1.2 判断查询--IF语句
首先看一下IF语句的用法,,IF语句查询结果可以作为该表的一个字段信息:
select name,age,if(age<25,"小鲜肉","老腊肉") as "age_description",if(salary>19000,"高端人群","低端人群")as class from person; # if(if_condition,True_result,False_result)三目运算符,条件为真,返回True_result,反之 False_result;
运行结果:
题目2:根据工资将人员分为高端人群和低端人群,显示效果: 姓名,年龄,薪资,级别;
select p1.name,p1.age,p1.salary,if(p1.salary>p2.salary,"高端人士","低端人士")as class from person as p1,(select dept_id,avg(salary)as salary from person group by dept_id) as p2 where p1.dept_id=p2.dept_id;
运行结果:
题目3. 根据工资高低,统计每个部门人员的收入情况,划分为富人,小资,平民,屌丝,四个级别 并且要求统计四个级别分别有多少人;
效果图:
先来介绍一下 多条件选择 select case when then...
语法一:select case "字段名" when "字段的条件 或者其中的一个值" then... when "字段的其他条件或者另外一个值" then... else ... end as "起一个别名作为字段" from “表”;
select *,case sex when "女" then "是女生啊" when "男" then "是男生啊" else "其他类别" end as "sex_description" from person;
运行结果:
语法二:select case when 字段条件 then .... when ...then... else...end as "起一个别名作为字段" from "表";
select name,age,salary,sex,case when salary<20000 then "屌丝" when salary between 20000 and 30000 then "小资" else "富人" END as "salary_description" # 给case...when ...then ...else ...end 起一个别名作为表person的一个字段 from person
运行结果:
select dept.name, sum(case when salary<19000 then 1 else 0 end )as "富人", sum(case when salary between 19000 and 24000 then 1 else 0 end) as "平民", sum(case when salary between 24000 and 19000 then 1 else 0 end) as "小资", sum(case when salary>29000 then 1 else 0 end) as "富人" from dept,person where dept.did=person.dept_id group by dept.did;
运行结果:
2. SQL逻辑查询语句的执行顺序;
伪代码:
select Distinct <select_list> from <left_table> <join_type> join <right_table> ON <join_condition> where <where_condition> Group by <group_by_list> Having <having_condition> Order by <order_by_condition> limit <limit_condition>
执行顺序:
from 表 ---> on 关联表的条件---->join 连接表----- where 表查询的条件 ---group by 分组原则----> having 分组条件--->select 查询表 ---->order by 排序原则----->limit 分页显示;