MySQL子查询的一些练习(未完)

查询平均工资最低的部门的信息和该部门的平均工资
SELECT中用相关子查询
SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) = (SELECT MIN(avg_sal)
                                                FROM(SELECT AVG(salary) avg_sal 
                                                     FROM employees
                                                     GROUP BY department_id
                                                     ) "t_dept_avg_sal"
                                               )
                          );

方式二：使用ALL
SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) <= ALL (SELECT AVG(salary) 
                                                     FROM employees
                                                     GROUP BY department_id
                                                    )
                         );

方式三：使用LIMIT
SELECT t1.*,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
FROM employees t1
WHERE t1.department_id = (SELECT department_id
                          FROM employees t2
                          GROUP BY department_id
                          HARING AVG(salary) = (SELECT AVG(salary) 
                                                FROM employees
                                                GROUP BY department_id
                                                ORDER BY AVG(salary) ASC
                                                LIMIT 0,1
                                               )
                         );
 方式四:多表的连接
 SELECT *,(SELECT AVG(salary) FROM employees t3 WHERE t3.department_id = t1.department_id) "AVG_SAL"
 FROM employees t1 JOIN (SELECT department_id,AVG(salary)
                         FROM employees t2
                         GROUP BY department_id
                         ORDER BY AVG(salary) ASC
                         LIMIT 0,1
                        )"t_dept_avg_sal"
 ON t1.department_id = t_dept_avg_sal.department_id;