mysql 查询实战1-解答

        对的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-题目》