mysql 查询实战1-解答

左手的ㄟ右手 2024-04-20 22:23 75阅读 0赞

对的[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 _1-]》

下一篇：《[mysql 查询实战2-题目][mysql _2-]》

[mysql _1-]: https://blog.csdn.net/qq_35461948/article/details/137565482?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22137565482%22%2C%22source%22%3A%22qq_35461948%22%7D
[mysql _2-]: https://blog.csdn.net/qq_35461948/article/details/137603185