mysql 案列解析

#1、查询最低工资的信息: last_name,salary
SELECT salary,last_name
FROM employees
WHERE salary=(
   SELECT MIN(salary)
   FROM employees
);
#2、查询平均工资最低的部门信息
#(1)查询平均工资的部门id和工资
SELECT AVG(e.salary) ag,e.department_id
FROM employees e
GROUP BY e.department_id;

#(2)得出平均工资中的最低工资
SELECT MIN(ag) FROM(
SELECT AVG(e.salary) ag,e.department_id id
FROM employees e
GROUP BY e.department_id
)AS s;

#(3)找出平均工资中的最低工资的 ID
SELECT s.id
FROM (
SELECT AVG(e.salary) ag,e.department_id id
FROM employees e
GROUP BY e.department_id
) AS s
WHERE s.ag = (SELECT MIN(ag) FROM(
SELECT AVG(e.salary) ag,e.department_id id
FROM employees e
GROUP BY e.department_id
)AS g );

#(4)找出id的全部信息

SELECT *FROM departments
WHERE department_id=(
   SELECT s.id
   FROM (
          SELECT AVG(e.salary) ag,e.department_id id
          FROM employees e
           GROUP BY e.department_id
        ) AS s
WHERE s.ag = (
    SELECT MIN(ag) FROM(
           SELECT AVG(e.salary) ag,e.department_id id
           FROM employees e
           GROUP BY e.department_id
    )AS g 
   )
);


#案列3、找出平均工资最低的部门信息和该部门的平均工资

#第二种解法
#(1)、找出平均工资按部门分 并排序得到最低工资
SELECT AVG(salary) AS 工资,department_id
FROM employees
GROUP BY department_id
ORDER BY 工资
LIMIT 0,1;
#(2)、找出他部门id(有可能不止一个)
SELECT s.department_id,s.工资
FROM (
 SELECT AVG(salary) AS 工资,department_id
 FROM employees
 GROUP BY department_id
 ORDER BY 工资
) AS s
WHERE s.工资=(
  SELECT AVG(salary) AS 工资
  FROM employees
  GROUP BY department_id
  ORDER BY 工资
  LIMIT 0,1
);

#3、找出平均工资和 其他部门信息

SELECT d.*,dst.工资
FROM departments d INNER JOIN (SELECT s.department_id,工资
  FROM (
  SELECT AVG(salary) AS 工资,department_id
  FROM employees
  GROUP BY department_id
  ORDER BY 工资
  ) AS s
  WHERE s.工资=(
  SELECT AVG(salary) AS 工资
  FROM employees
  GROUP BY department_id
  ORDER BY 工资
  LIMIT 0,1
  ) ) AS dst
WHERE d.department_id=dst.department_id
;