|
阅读：6951回复：0
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)； 注：多列子查询，SQL Server不支持，Oracle可以支持 不过SQL 可以通过checksum来实现同样的效果，代码如下： select deptno,ename,job,sal FROM EMP WHERE checksum(deptno,sal) IN (SELECT checksum(deptno,sal) from (select deptno,MAX(sal) as sal FROM EMP GROUP BY deptno)t) 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')； 来源：http://blog.csdn.net/devercn/article/details/22986 |
|
|
公众号
