网站设计要求,做网站的费用入账,网站建设8万属于资产吗,网站流量排名 全球目录
1.每位经理的下属员工数量 2.员工的直属部门 3.判断三角形 4.上级经理已离职的公司员工 5.换座位
6.电影评分
7.修复表中的名字 8.患某种疾病的患者
9.删除重复的电子邮箱 1.每位经理的下属员工数量 # Write your MySQL query statement below
#e1是经理#xff0c;…目录
1.每位经理的下属员工数量 2.员工的直属部门 3.判断三角形 4.上级经理已离职的公司员工 5.换座位
6.电影评分
7.修复表中的名字 8.患某种疾病的患者
9.删除重复的电子邮箱 1.每位经理的下属员工数量 # Write your MySQL query statement below
#e1是经理e2是员工
select e1.employee_id,e1.name,count(e2.employee_id) as reports_count,round(avg(e2.age)) as average_age
from Employees e1
join Employees e2
on e1.employee_ide2.reports_to
group by e1.employee_id,e1.name
order by e1.employee_id; 2.员工的直属部门 # Write your MySQL query statement below
select employee_id,if(count(department_id)1,department_id,max(case primary_flag when Y then department_id end)) as department_id
from Employee
group by employee_id 3.判断三角形 # Write your MySQL query statement below
select x,y,z,casewhen xyz and xzy and yzx then Yeselse Noend as triangle
from Triangle; 4.上级经理已离职的公司员工 # Write your MySQL query statement below
#子查询就是查询套查询
select employee_id
from Employees
where salary30000
and manager_id not in (select employee_id from Employees)
order by employee_id 5.换座位 # Write your MySQL query statement below
select s1.id as id,#逻辑如果没有下一位且当前是奇数不改动奇数取下一位的值偶数取上一位的值if(s2.id is null s1.id%21,s1.student,if(s1.id%21,s2.student,s3.student)) as student
from Seat s1
left join Seat s2 on s2.ids1.id1
left join Seat s3 on s3.ids1.id-1
order by id
6.电影评分 # Write your MySQL query statement below
#查找用户
(select u.name as results
from Users u
left join MovieRating mr
on u.user_idmr.user_id
group by u.user_id
order by count(*) desc,name asc
limit 1
)
#查找电影
union all
(
select title as results
from Movies m
left join MovieRating mr
on m.movie_idmr.movie_id and year(mr.created_at)2020 and month(mr.created_at)2
group by mr.movie_id
order by avg(mr.rating) desc,title asc
limit 1
)
7.修复表中的名字 # Write your MySQL query statement below
#使用substring分割字符使用upper,lower大小写使用concat连接
select user_id,concat(upper(substring(name,1,1)),lower(substring(name,2))) name
from Users
order by user_id 8.患某种疾病的患者 # Write your MySQL query statement below
select *
from Patients
where conditions regexp ^DIAB1|\\sDIAB1 9.删除重复的电子邮箱 # Write your MySQL query statement below
#delete删除
delete p1
from Person p1,Person p2
where p1.emailp2.email and p1.idp2.id
