五、高级查询和连接
1.每位经理的下属员工数量
题目描述
题目思路:
题目要求是统计向经理汇报的员工人数,以及这些员工的平均年龄
1.将经理和其管理的下属连接在一张表上,并根据经理id进行分组
2.统计经理的员工人数和平均年龄
代码编写
select
a.employee_id,
a.name,count(b.reports_to) as reports_count,
round(avg(b.age),0)as average_age
from Employees a join Employees b
on b.reports_to=a.employee_id
group by a.employee_id
order by a.employee_id
2.员工的直属部门
题目描述
题目要求是查出员工所属的直属部门。
将员工分为两类分析,一类是只加入一个部门的,这类员工的直属部门就是他加入的唯一的部门;一类是加入多个部门的,这类员工的直属部门是枚举类型为 primary_flag="Y"的部门。
代码编写
elect
employee_id,
department_id
from Employee
where employee_id in
(select employee_id from Employee
group by employee_id
having count(department_id)=1)
or primary_flag="Y"
3.判断三角形
题目描述
代码编写
select
x,
y,
z,
(case when x+y>z and x+z>y and y+z>x then "Yes"
else "No" end) as triangle
from triangle
4.连续出现的数字
题目描述
代码编写
官方解法(代码来源leetcod官方):
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
官方解法直接用三表连接解决,简单粗暴,但不严谨,存在两个明显的漏电:
第一,当id并不连续,现实中常常会因为某些问题删除某行,似的id不连续,比如上一个id为5,下一个id为7;
第二,当一个数字连续出现三次以上,如5次,10次,n次;
在以上情况下,这种解法并不能完成这道题目的要求。
做法一(利用 lag ( ),lead ( )函数):
select distinct num as ConsecutiveNums
from (
select id,num,
lag(num) over (order by id) as num_per,
lead(num) over (order by id) as num_next
from Logs) a
where num=num_per and num=num_next
‘’
思路:在表格查找上下行都与该行的数值相等,并返回该数值的不重复结果 ,这种解法可以忽视id是否连续的问题,也不用考虑出现次数多于3次的问题,是个人感觉最简单快捷的方法。
Tip:
lag( ) ,查询当前行向上偏移n行后的结果。
lag(expression,offset,default_value),expression为待查询的列,offset为向上偏移的行数,default_value:为超出最上边界的默认值。
lead ( ) ,查询当前行向下偏移n行后的结果。
lead(expression,offset,default_value),expression为待查询的列,offset为向上偏移的行数,default_value:为超出最上边界的默认值。
做法二(row_number( ) over( ) ):
select distinct num as ConsecutiveNums
from(
select id,num,
(row_number() over(order by id)-
row_number() over(partition by num order by id)) as series
from logs
) a
group by num,series
having count(series)>2
思路: 首先,用row_number( ) over( )对id进行重新排序,确保id是连续的;其次,用row_number( ) over()按照数字num分组并对id进行排序;再者,用两者的排序相减,如果数字连续出现,得到的差是相等的,最后根据num,series分组并统计series相同值的次数,筛选出大于或等于3次的情况。
5.指定日期的产品价格
题目描述
代码编写
做法一:
select p1.product_id,ifnull(new_price,10) as price
from(
select distinct product_id from Products) as p1
left join(
select product_id,new_price from Products
where (product_id,change_date) in(
select product_id,max(change_date) from Products
where change_date<="2019-08-16"
group by product_id
)
)as p2
on p1.product_id=p2.product_id
思路:筛选出不重复的商品id作为表一,筛选商品id和每个商品在2019-08-16的日期之前的最新价格(如果商品在2019-08-16之后上架,则商品价格等于默认值)作为表二,将表一,二连接,并筛出商品id和售价,如果找不到商品的价格信息,商品价格默认为10。
做法二:
select distinct product_id, if(rnew_date is null, 10, new_price) as price
from (
select *, rank() over(partition by product_id order by rnew_date desc) as series
from (
select *, if(change_date > '2019-08-16', null, change_date) as rnew_date
from Products
) as a
) as b
where series = 1
思路: 每个商品在2019-08-16的日期之前的最新价格(如果商品在2019-08-16之后上架,则商品,更新日期默认为null),对商品id分组并根据更新日期降序排序,筛选日期序号为1(日期序号为1,说明这是该商品售价更新的最新日期)的商品id和售价的信息,如果商品的更新日期为null,商品价格默认为10。
6.最后一个进入巴士的人
题目描述
代码编写
select person_name from(
select *,
sum(weight) over(order by turn) as total from Queue
) as a
where total<=1000
order by turn desc
limit 1
思路:按照上车顺序 turn 将表格排序 ,计算出已上车的人的总质量,并限制总质量只能在1000以内,将符合要求的上车的人的顺序倒序排列,并选出第一条信息的乘客姓名。
7.按分类统计薪水
题目描述
代码编写
select 'Low Salary' as category,
sum(case when income<20000 then 1 else 0 end)as accounts_count
from Accounts
union
select 'Average Salary' as category,
sum(case when income<=50000 and income>=20000 then 1 else 0 end)as accounts_count
from Accounts
union
select 'High Salary' as category,
sum(case when income>50000 then 1 else 0 end)as accounts_count
from Accounts
思路:依次返回每一类的查询结果,并用 UNION
函数 将这三个单独查询的结果组合在一起。