SQL子查询的一些例子

1、单行子查询
        select ename,deptno,sal
        from emp
        where deptno=(select deptno from dept where loc='NEW YORK');

     2、多行子查询
        SELECT ename,job,sal
        FROM EMP
        WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%');

     3、多列子查询
        SELECT deptno,ename,job,sal
        FROM EMP
        WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno);

     4、内联视图子查询
       (1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal);
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5;
 
     5、在HAVING子句中使用子查询
        SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN');

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值