MySQL難點語法——子查詢

本篇主要通過練習來講解子查詢的知識，進入正題之前，先熟悉數據表，表格的數據可以先不用管，主要是熟悉表格的欄位名

 這裡子查詢分為三個部分：

　　1、where條件子查詢

　　　　　　這個子查詢和普通的查詢沒什麼區別，主要是先讀懂題目的意思，然後將結果集組裝起來

　　　　　　　　需求：1.查看職員名稱和名字為chang的員工一樣的所有員工的id和名字

select id, last_name, title 
from s_emp 
where title=(     # 和chang員工一樣的職位
    select title 
    from s_emp 
    where last_name = "chang"
);

　　　　　　　　需求：2.查看員工工資小於平均工資的所有員工的id和名字

select id, last_name, salary
from s_emp
where salary<(    # 員工的平均工資
    select avg(salary)
    from s_emp
);

　　　　　　　　需求：3.查看部門與員工名字為Chang的員工所在部門相同，或者與區域為2的部門相同的部門所有員工的id和 名字

select id, last_name, title, dept_id
from s_emp
where dept_id = (     # chang員工所在的部門
    select dept_id
    from s_emp
    where last_name = "chang"
) or dept_id in (       # 區域為2的部門
    select dept_id
    from s_dept
    where region_id=2
);

　　　　　　　　需求：4.查看部門平均工資大於32號部門平均工資的部門id

select dept_id,salary
from s_emp
group by dept_id
    having avg(salary)>(   # 32部門的平均工資
        select avg(salary)
        from s_emp
        where dept_id = 32
    );

　　　　　　　　需求：5.查看工資大於Smith所在部門平均工資的員工id和姓名

select id, last_name
from s_emp
where salary>(         # smith所在的部門的平均工資
    select avg(salary)
    from s_emp
    where dept_id = (   # smith所在的部門
        select dept_id
        from s_emp
        where last_name = "Smith"
    )
);

　　　　　　　　需求：6.查看薪資高於Chang員工經理薪資的員工信息

select id, last_name, salary
from s_emp
where salary>(     #chang員工的經理的工資
    select salary
    from s_emp
    where id=(         # chang員工的經理
        select manager_id
        from s_emp
        where last_name = "chang"
    )
);

　　　　　　　　需求：7.查看薪資高於（Chang員工經理的經理所在區域的）最低工資的員工的信息

select id, last_name, salary
from s_emp
where salary>(
    select salary
    from s_emp
    where dept_id in (     # chang員工的經理的經理所在的部門的所在區域的所有部門
        select id
        from s_dept
        where region_id = (     # chang員工的經理的經理所在的部門的所在區域
            select region_id
            from s_dept
            where id=(    # chang員工的經理的經理所在的部門      
                select dept_id
                from s_emp
                where id=(     # chang員工的經理的經理
                    select manager_id
                    from s_emp
                    where id=(   # chang員工的經理
                        select manager_id
                        from s_emp
                        where last_name="chang"
                    )
                )
            )
        ) 
    ) order by salary limit 0,1
);

　　　　　　　　需求：8.查看所有客戶負責員工的總工資

select sum(salary) 
from s_emp 
where id in (       # 員工負責的id
    select sales_rep_id 
    from s_customer   
);

　　　　　　　　需求：9.查看工資大於客戶負責員工最高工資的員工信息

select id, last_name 
from s_emp 
where salary>(     # 員工負責的id中工資最高的薪資
    select max(salary) 
    from s_emp 
    where id in (    # 員工負責的id
        select sales_rep_id 
        from s_customer
    )
);

　　　　　　　　需求：10.查看客戶負責員工中工資大於Chang員工的工資的員工信息

select id, last_name, salary
from s_emp
where id in (       # 員工負責的id
    select sales_rep_id
    from s_customer
) and salary > (        # 工資大於chang員工的工資
    select salary
    from s_emp
    where last_name="chang"
);

　　　　　　　　需求：11.查看部門平均工資大於Chang所在部門平均工資的部門id

select dept_id, avg(salary)
from s_emp
group by dept_id
    having avg(salary) > (    # chang員工所在部門的平均工資
        select avg(salary)
        from s_emp
        where dept_id=(    # chang員工所在部門
            select dept_id
            from s_emp
            where last_name="chang"
        )
    );

　　　　　　　　需求：12.查看Chang員工所在部門其他員工薪資總和

select sum(salary)
from s_emp
where dept_id=(         # chang員工所在的部門
    select dept_id
    from s_emp
    where last_name="chang"
) and last_name != "chang";   # 除了chang員工

　　　　　　　　需求：13.查詢工資大於41號部門平均工資的員工,並且該員工所在部門的平均工資也要大於41號部門的平均工資

select id,last_name,salary, dept_id
from s_emp
where salary > (             # 高於41號部門的平均工資
    select avg(salary)
    from s_emp
    where dept_id = 41
) and dept_id in (           # 高於41號部門的平均工資的部門
    select dept_id
    from s_emp
    group by dept_id
        having avg(salary)>(       # 41號部門的平均工資
            select avg(salary)
            from s_emp
            where dept_id = 41
        )
);

 　　　　2、數據集條件子查詢

　　　　　　　　需求：1.求平均薪水最高的部門的id

select dept_id 
from s_emp 
group by dept_id 
    having avg(salary) = (       # 部門平均工資表中的最大工資
        select max(max_avg)
        from (       # 部門平均工資表
            select avg(salary) as max_avg
            from s_emp
            group by dept_id
        ) as newtable
);
# 排序搜尋的部門
select dept_id
from s_emp
group by dept_id
order by avg(salary) desc
limit 0, 1;

　　　　　　　　需求：2.求平均薪水最高的部門的部門名稱

select id, name
from s_dept
where id=(              # 部門平均薪資表中最高的平均工資的部門
    select dept_id
    from s_emp
    group by dept_id
        having avg(salary) = (   # 部門平均薪資表中最高的平均工資
            select max(avgs)
            from (        # 部門平均薪資表
                select avg(salary) avgs
                from s_emp
                group by dept_id
            ) as newtable
        )
);
select id, name
from s_dept
where id=(
    select dept_id
    from s_emp
    group by dept_id
    order by avg(salary) desc
    limit 0, 1
);