sql子查詢
1. 包含在select子句中的子查詢:
SQL> select dname,(select max(sal) from emp where deptno=dept.deptno) max_sal
2 from dept
3 /
DNAME MAX_SAL
-------------- ----------
ACCOUNTING 5000
RESEARCH 3000
SALES 2850
OPERATIONS
可以在顯示每行指定列的同時,顯示分組函式。
與下面查詢有類似效果,但下面查詢是顯示的deptno列:
SQL> select deptno,max(sal) from emp
2 group by deptno
3 /
DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
與下面示例也有類似的效果,但這裡的第一列只顯示了三行:
SQL> select d.dname, max(e.sal)
2 from emp e, dept d
3 where e.deptno=d.deptno
4 group by dname
5 /
DNAME MAX(E.SAL)
-------------- ----------
ACCOUNTING 5000
RESEARCH 3000
SALES 2850
下面示例可以代替emp與dept兩個表的連線查詢:
SQL> select ename,(select dname from dept where deptno=emp.deptno) dname
2 from emp
3 /
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
下面是與上面查詢相同效果的表的連線查詢:
SQL> select e.ename,d.dname
2 from emp e, dept d
3 where e.deptno=d.deptno
4 /
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
下面select語句中的子查詢更能體現其作用:
SQL> select ename, sal, (select avg(sal) from emp) avgsal
2 from emp
3 /
SQL> select ename, sal,
2 (select avg(sal) from emp where emp.deptno=emp1.deptno) avgsal
3 from emp emp1
4 /
2. 包含在where子句中的子查詢:
SQL> select dname
2 from dept
3 where deptno=(select deptno from emp)
4 /
where deptno=(select deptno from emp)
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one ro
當where條件中使用=、 、 !=、 >=、 <=時,要保證子查詢返回的結果唯一,否則可以使用in,或者使用any、some、all等字首處理:
SQL> select dname
2 from dept
3 where deptno in (select deptno from emp)
4 /
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
SQL> select ename
2 from emp
3 where sal>all(select sal from emp where deptno=30)
4 /
ENAME
----------
JONES
SCOTT
KING
FORD
SQL> select dname
2 from dept
3 where deptno in
4 (
5 select deptno from emp
6 group by deptno
7 having count(empno)>3
8 )
9 /
DNAME
--------------
SALES
RESEARCH
3. 包含在from子句中的子查詢:
統計emp表中,每年入職的員工人數:
SQL> select to_char(hiredate,'yyyy') hireYear,count(empno)
2 from emp
3 group by hireYear
4 /
group by hireYear
*
ERROR at line 3:
ORA-00904: "HIREYEAR": invalid identifier
因為from子句最先處理,然後是group by子句,最後是select子句,所以上述group by子句中使用的列別名還不可見,從而出現錯誤,我們可以改為在from子句中使用子查詢解決:
SQL> select hireYear,count(empno)
2 from (select to_char(hiredate,'yyyy') hireYear, empno from emp)
3 group by hireYear
4 /
HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10
也可以使用表別名,使得語法更通順:
SQL> select hireYear,count(empno)
2 from (select to_char(hiredate,'yyyy') hireYear, empno from emp) d
3 group by hireYear
4 /
HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10
另外子查詢也可以巢狀命名:
SQL> select hireYear,count(empno)
2 from
3 (select to_char(hiredate,'yyyy') hireYear,empno
4 from (select * from emp) d1
5 ) d2
6 group by hireYear
7 /
HIRE COUNT(EMPNO)
---- ------------
1987 2
1980 1
1982 1
1981 10
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/37724/viewspace-152592/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL查詢的:子查詢和多表查詢SQL
- SQL--子查詢SQL
- Sql Server系列:子查詢SQLServer
- sql語法相關子查詢與非相關子查詢SQL
- sql遞迴查詢子級SQL遞迴
- 子查詢-表子查詢
- sql-server不相關子查詢SQLServer
- sql-server相關子查詢SQLServer
- SQL語言基礎(子查詢)SQL
- 什麼是SQL 語句中相關子查詢與非相關子查詢SQL
- ORACLE_OCP之SQL_子查詢OracleSQL
- SQL -- 使用聯結還是子查詢?SQL
- 複雜查詢—子查詢
- SQL入門之4 group by 與子查詢SQL
- OCP課程8:SQL之使用子查詢SQL
- 相關子查詢&非相關子查詢概念
- 一文終結SQL 子查詢優化SQL優化
- 【學習】SQL基礎-007-子查詢SQL
- SQL筆記之 子查詢080812SQL筆記
- 涉及子查詢sql的一次優化SQL優化
- MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換MySql
- MySQL子查詢MySql
- 子串查詢
- 使用子查詢
- MYsql 子查詢MySql
- oracle子查詢Oracle
- 查詢子串
- 子查詢分解
- 11子查詢
- informix子查詢ORM
- MySQL聯結查詢和子查詢MySql
- select查詢之三:子查詢
- mysql sql_safe_updates 不支援子查詢的更新。MySql
- PLSQL Language Referenc-PL/SQL靜態SQL-帶有子查詢的查詢結果集處理SQL
- 【SQL查詢】集合查詢之INTERSECTSQL
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- 區分關聯子查詢和非關聯子查詢
- exist-in和關聯子查詢-非關聯子查詢