10_mysql子查询常用的4中方式

题目：查询平均工资最低的部门信息

1.普通子查询

#方式1：
SELECT *
FROM departments
WHERE department_id = (
            SELECT department_id
            FROM employees
            GROUP BY department_id
            HAVING AVG(salary ) = (
                        SELECT MIN(avg_sal)
                        FROM (
                            SELECT AVG(salary) avg_sal
                            FROM employees
                            GROUP BY department_id
                            ) t_dept_avg_sal

                        )
            );

#方式2：ALL关键字
SELECT *
FROM departments
WHERE department_id = (
            SELECT department_id
            FROM employees
            GROUP BY department_id
            HAVING AVG(salary ) <= ALL(
                        SELECT AVG(salary)
                        FROM employees
                        GROUP BY department_id
                        )
            );

#方式3： LIMIT
SELECT *
FROM departments
WHERE department_id = (
            SELECT department_id
            FROM employees
            GROUP BY department_id
            HAVING AVG(salary ) =(
                        SELECT AVG(salary) avg_sal
                        FROM employees
                        GROUP BY department_id
                        ORDER BY avg_sal ASC
                        LIMIT 1        
                        )
            );

#方式4：连接+子查询
SELECT d.*
FROM departments d,(
        SELECT department_id,AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal ASC
        LIMIT 0,1
        ) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.department_id