内连接的例子分析

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"
)

这个是求出平均工工资最高的部分的信息