MySQL子查詢的一些練習(未完)

1.查詢平均工資最低的部門的資訊和該部門的平均工資
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 #找最低部門的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 #找最低部門的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 #找最低部門的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;                        
 
 2.查詢公司manger的id
#方式一：自連接
SELECT DISTINCT t1.empolye_id,
FROM employees t1 JOIN employees t2
WHERE t1.employee_id = t2.manger_id;

#子查詢
SELECT T1.employee_id
FROM emplpoyees t1
WHERE t1.employee_id IN (SELECT DISTINCT t2.manger_id
                         FROM employees t2
                        );

#使用EXISTS          有 (NOT)IN 的時候通常可以改成 (NOT)EXISTS
SELECT T1.employee_id
FROM emplpoyees t1
WHERE EXISTS(SELECT *
             FROM employees t2
             WHERE t1.employee_id = t2.manger_id
            );
             
3.查詢各個部門 中最高工資中最低的那個部門的 最低工資是多少 (與第一題類似，方法在這里就只寫一種了)

#思路：先找出每個部門工資最高的數，再找其中工資最低的部門，然后在該部門中尋找低的工資
SELECT MIN(salary)
FROM emplpyees 
WHERE deparment_id = (SELECT deparment_id #找到部門的ID
                      FROM employees
                      GROUP BY depaetment_id
                      HARING MAX(salary) = (SELECT max(salary) max_sal                                                                           FROM employees #找到最高工資中最低的那個部門的工資
                                            GROUP BY department_id
                                            ORDER BY max_sal ASC
                                            LIMIT 1
                                           )
                    );

標籤：其他

上一篇：redis主從復制

下一篇：Mysql 開啟慢日志查詢及查看慢日志 sql