内連接配接的例子分析

select top 3 "E".ename,"E".sal,"S".GRADE,"D".dname
from emp "E"
join SALGRADE "S"
on "E".sal>="S".LOSAL and "E".sal<="S".HISAL
join dept "D"
on "E".deptno="D".deptno
where "D".dname not like '%o%'
order by "E".sal desc
           

第一行top 3是顯示前三行的資料

“E”“S” “D”都是取别名

from 。。。 join 是将兩張表連接配接在一起 on是連接配接條件

where “D”.dname not like ‘%o%’是對内連接配接後的結果進行篩選

最後的結果有點出乎意料不是 小于 3 而是還是三行資料 因為top 3的原因 意思就是如果結果數量夠多的話，即使用where将資料過濾一部分 到最後還是會有後面的資料頂上來替換掉原來的資料 是以最後顯示的資料還是3行

order by “E”.sal desc 是按照”E”.sal的降序排序

注意點：

where “D”.dname not like ‘%o%’不能放在from後面 我也不知道為什麼

而在group 分組中卻要from後面可以在分組前對資料進行篩選

select "T".deptno,"T".sal1,"S".grade
from(
    select deptno,avg(sal) "sal1"  //取别名 
    from emp
    group by deptno
)"T"  //臨時表取别名
join "salgrade" "S"
on "T".sal1 between "S".losal and "S".hisal           

“T”是臨時表，可以當作一個整體來用 然後就是“T”和“S”的組合

select top 3 *
from(select *
from emp
where sal>(select MIN(sal) from emp)
) "T"
join dept "D"
on "T".deptno="D".deptno
join SALGRADE "S"
on "T".sal between "S".LOSAL and "S".HISAL
order by "T".sal asc           

select *

from emp

where sal>(select MIN(sal) from emp)是一個臨時表

order by “T”.sal asc是按照sal的降序排序

select top 3 *是顯示前三個的資料

select "E".*
    from (  
            select deptno, avg(sal) "avg_sal" 
                from emp 
                group by deptno 
        ) "E"
    where "E"."avg_sal" = (
            select max("avg_sal") from (select deptno, avg(sal) "avg_sal" from emp group by deptno) "T"
    )           

這個是内嵌套

select "T".*,"D".dname,"S".GRADE
from (
select top 2 "E".deptno,AVG(sal) "sal1"
from emp "E"
join dept "D"
on "E".deptno="D".deptno
join SALGRADE "S"
on "E".sal between "S".LOSAL and "S".HISAL
where "E".sal>1500
group by "E".deptno
having AVG("E".sal)>2000
order by AVG("E".sal) desc
)"T"
join
dept "D"
on "D".deptno="T".deptno
join SALGRADE "S"
on T.sal1 between "S".LOSAL and "S".HISAL           

select top 2 "E".deptno,AVG(sal) "sal1"
from emp "E"
join dept "D"
on "E".deptno="D".deptno
join SALGRADE "S"
on "E".sal between "S".LOSAL and "S".HISAL
where "E".sal>1500
group by "E".deptno
having AVG("E".sal)>2000
order by AVG("E".sal) desc           

是臨時表 有個問題就是 如果它是單獨的話 去掉top2會顯示全部

但是 如果它是嵌套使用的話 如例子 如果去掉top 2她就會報錯 除非去掉order

select *
from (
    select deptno,AVG(sal) "avg_sal"
    from emp
    group by deptno
)"T"
where "T".avg_sal=(
select MAX("E"."avg_sal") from( select deptno,AVG(sal) "avg_sal"
from emp
group by deptno
)"E"
)           

這個是求出平均工工資最高的部分的資訊