MySQL 查询小栗子

# 头脑风暴小练习
# 查询平均工资最低的部门信息

# 方式 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 ：
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 1
                	)t_dept_avg_sal
WHERE d.department_id = t_dept_avg_sal.department_id;