Oracle×Ó²éѯ
×Ó²éѯ
µ¥ÐÐ×Ó²éѯ(single-row subqueries)
ʹÓõÄÔËËã·ûºÅ(=,>,<,>=,<=,<>)
¶àÐÐ×Ó²éѯ(multiple-row subqueries)
ʹÓõÄÔËËã·ûºÅ(in,not in,exists,not exits,all,any)
Ïà¹Ø×Ó²éѯ(correlated subqueries)
¸ñʽ select ÁÐÃû,(select Óï¾ä) from ±íÃû
±êÁ¿×Ó²éѯ(scalar subqueries)
×Ó²éѯÊÇ·µ»Øµ¥Ðе¥ÁÐ,¸ñʽͬÉÏ
¶àÁÐ×Ó²éѯ(multiple-column subqueries)
ÔÚDDLÓï¾äÖÐʹÓÃ×Ó²éѯ
ÔÚDMLÓï¾äÖÐʹÓÃ×Ó²éѯ
--------
µ¥ÐÐ×Ó²éѯ
--ÏÔʾ¹¤×Ê×î¸ßµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal from emp
Where sal=(select max(sal) from emp);
--------
¶àÐÐ×Ó²éѯ
--ÏÔʾÓ벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp
Where job in (select distinct job from emp where deptno=20);
--ÏÔʾ²»Ó벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄËùÓйÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>all(select sal from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄÈκιÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>any(select sal from emp where deptno=20);
---------
Ïà¹Ø×Ó²éѯ
--ÏÔʾÿ¸ö²¿ÃŵÄ×î¸ß¹¤×ʵĹÍÔ±ÐÅÏ¢
select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--Ôö¼Ódistinct
select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--ÏÔʾ¹¤×÷ÔÚNEW YORKµÄ¹ÍÔ±ÐÅÏ¢
select ename,deptno,sal,job from emp
where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');
---------
±êÁ¿×Ó²éѯ
--·µ»Øµ¥Ðе¥ÁÐ
Select count(*) from emp;
Select sum(sal)
Ïà¹ØÎĵµ£º
ƽʱ½Ó´¥mysql½Ï¶à£¬ÒòΪmysqlÔÚÒ»°ãµÄСÏîÄ¿Öй»ÓÃÁË ¿ªÔ´¶øÇÒÃâ·Ñ¡£
ºÜ¶à¹«Ë¾ÐèÒªoracleά»¤µÄ£¬¿ª·¢oracleÉõÖÁÊǶþ´Î¿ª·¢ÔÚ¹úÄÚÒ²±È½ÏÉٵģ¬Óм׹ÇÎĹ«Ë¾£¬Ë¸ÒºÍËûÃÇÇÀ·¹Íë°¡ ¹þ¹þ ²»¹ýÈ˼ҵÄÊÕ·ÑÂù¹óµÄ£¬ÄãÈç¹ûÓÐˮƽ£¬³Ôµã²Ð¸þµÄ»ú»áÂù¶à¡£ oracleµÄÅàѵÐÅÏ¢±í´ïÁËÊг¡ÐèÇó¡£
£¨ÎÒÔÚѧУ»·¾³µÄ²ÂÏ룬רҵÈËÊ¿À´ÅÄש ......
ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖØ¸´¼Ç¼£¬µ«Ö»ÄÜȡһ¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......
http://blog.csdn.net/mustbelove/archive/2007/04/17/1567836.aspx(ת)
1 year=1*12 months
1 day=24
hours=24*(1*60mins)=24*60*(1*60 seconds)
1 week =7 days
×¢Ò⣺
ºÚÉ«×ÖÌåÊÇ oracle
8i£¬9i¶¼¿ÉÒÔʹÓõĺ¯Êý£¬
À¶É«×ÖÌåÊÇ Oracle
9i
ÐÂ
ÔöµÄ²¿·Ö¡£
Çë´ó¼ÒÔÚʹÓÃʱ£¬×¢Òâ°æ±¾ÏÞÖÆ¡£
add_mon ......
oracle·ÖÎöº¯ÊýÊ®·ÖÇ¿´ó£¬ÎÒÃÇÖ»ÒªÕÆÎÕÕâЩ·½·¨£¬¸üÖ±½ÓµÄ˵·¨¾ÍÊÇÖªµÀÕâЩ·ÖÎöº¯ÊýµÄ×÷ÓþÍÄÜÍê³ÉºÜ¶à¹¤×÷¡£
ϱßÌù³öÕâЩº¯Êý£¬¼°¼òµ¥Ó¦Óá£
ÆäÖÐÎÒÏë¶Ôlag£¨£©ºÍlead£¨£©º¯Êý×øÏÂ˵Ã÷£ºlag£¨£©±¾ÉíÊÇÑÓºóµÄÒâ˼Ҳ¾ÍÊÇÑÓºó³öÏÖijÁеÄÊý£¬¶ølead£¨£©ÓÐÒýÁì¡¢ÁìÏȵÄÒâ˼Ҳ¾ÍÊÇÌáǰ¼¸ÐÐÏÔʾijÁÐÊý¾Ý
RANK()
dense_rank() ......
Ë÷Òý
Ë÷ÒýµÄÄ¿µÄÊÇÌá¸ß´æ´¢Êý¾ÝÒ³ÃæµÄËÙ¶È
·þÎñÆ÷ɨÃèË÷ÒýÒÔ»ñµÃÊý¾Ý´æ´¢µ¥ÔªµÄµØÖ·
Ë÷ÒýµÄÓŵãΪ£ºÌá¸ß²éѯµÄÖ´ÐÐËÙ¶È¡¢ÊµÊ©Êý¾ÝµÄΨһÐÔ¡¢¼ÓËÙÁ˱íÖ®¼äµÄÁ¬½Ó
Ë÷ÒýµÄȱµãΪ£º´´½¨Ë÷ÒýÐèÒª»¨·Ñ½Ï¶àµÄʱ¼ä¡¢Ã¿¸ö´´½¨µÄË÷ÒýÁ¬Í¬ÔÏȵÄÊý¾ÝÔ´¶¼ÐèÒª¿Õ¼äÀ´´æ´¢Êý¾Ý¡¢Ã¿´ÎÐÞ¸ÄÊý¾ÝʱË÷Òý¶¼Òª¸üÐÂ
......