对的mysql 查询实战1-题目,进行解答
1,查询部门工资最高的员工
1,按部门分组,先查出部门薪资最高的:
select dept_id, max(salary) from employee
group by dept_id;
2,等值连接查询:关联查部门信息
select
b.name as department_name,
a.name as employee_name,
c.max_salary
from
employee as a,
department as b,
(select dept_id, max(salary) AS max_salary
from employee
group BY dept_id
) AS c
WHERE a.dept_id = c.dept_id
AND a.dept_id = b.dept_id
AND a.salary = c.max_salary
3,连接查询
select
b.name as department_name,
a.name as employee_name,
c.max_salary
from employee as a join
(
select dept_id, max(salary) as max_salary
from employee
group by dept_id
) as c
on a.dept_id = c.dept_id and a.salary = c.max_salary
join department as b
on a.dept_id = b.id;
4,用子查询:
select d.name as department_name,
e.name as employee_name,
e.salary as max_salary
from employee as e
join department as d
on e.dept_id=d.dept_id
where (e.dept_id, e.salary)
in(
select dept_id, max(salary) from employee
group by dept_id
);
2,部门工资前三高的员工
1,查出比我工资高的人数
select e1.id, count(e2.salary)
from employee e1, employee e2
where e1.dept_id = e2.dept_id and e2.salary > e1.salary
group by e1.id;
2,关联查询 比我工资高的< 3
select b.name as department_name,
a.name as employee_name,
a.salary
from employee a
join department b on a.dept_id = b.dept_id
where(
3 > (select count( distinct a.salary )
from employee as c
where a.salary < c.salary
and c.dept_id = a.dept_id)
order by a.dept_id, a.salary desc;
3,查出id后进行过滤
select a.name as employee_name,
b.name as department_name,
a.salary
from employee as a join department as b
on a.dept_id = b.dept_id
where a.id in (
select e1.id from employee e1 left join employee e2
on e1.dept_id = e2.dept_id and e2.salary > e1.salary
group by e1.id having count(distinct e2.salary)<3
);
3,删除重复的电子邮箱
要求:删除重复的电子邮箱,相同的保留id最小的那条
1,分组后,找出id最小值的
SELECT id FROM (SELECT MIN(id) AS id FROM USER GROUP BY email) AS tmp;
2,思路1: 分组后,找出id最小值的,然后删除id不在这里面的。
delete from user where id not in(select id from(select min(id)
from user group by email)
as tmp);
3,思路2:把查询结果作为中间表,进行左连接,
select * from user as u left join (select min(id) as id
from user group by email) as tmp
on u.id=tmp.id;
-- id为null的值,是要删除的
SELECT * FROM USER AS u LEFT JOIN (SELECT MIN(id) AS id
FROM USER GROUP BY email) AS tmp
ON u.id=tmp.id WHERE tmp.id IS NULL;
delete u from user as u left join (select min(id) as id
from user group by email) as tmp
on u.id=tmp.id where tmp.id is null;
思路3: 跟自己做比较,邮箱一样的,删除大的。
select u1.* from user u1, user u2
where u1.email = u2.email and u1.id > u2.id;
delete u1 from user u1, user u2
where u1.email = u2.email and u1.id > u2.id;
4、统计各专业学⽣⼈数
1,统计各科的人数:
select lesson_id,count(1) as student_number from learning group by lesson_id;
2, 用查询结果作为中间表关联
select a.name, ifnull(b.student_number,0) as student_number
from lesson as a left join
(select lesson_id,count(1) as student_number
from learning group by lesson_id) as b on a.id=b.lesson_id
order by b.student_number desc, a.name;
3,直接关联查询:
select a.name, count(b.student_id) as student_number
from lesson as a left join learning
as b on a.id=b.lesson_id group by a.name,a.id
order by student_number desc, a.name;
5、查找⾄少有三名直接下属的经理
1,自关联分组查询
-- 经理也是员工, 两个id相同
select a.name from employee as a
left join employee as b on a.id = b.manager_id
group by a.id having count(b.id) >= 3 ;
2,子查询
select name from employee
where id in (select manager_id from employee
group by manager_id having count(1)>=3);
总结:
查询的时候,完成sql后,可以再想想是否还有其他的方式可以去处理。有子查询的,多半也是可以用关联查询的。
上一篇:《mysql 查询实战1-题目》
下一篇:《mysql 查询实战2-题目》