[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
Èç¹ûÎÒÃǰ´ÕÕʾÀýÏëµÃµ½Ã¿¸ö²¿ÃÅнˮֵ×î¸ßµÄ¹ÍÔ±µÄ¼Í¼£¬¿ÉÒÔÓÐËÄÖÖ·½·¨ÊµÏÖ£º
ÏÈ´´½¨Ê¾Àý±í
create table emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key(empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
·½·¨1.empÖеÄÿһÐж¼»á½øÐÐmax±È½Ï£¬·Ñʱ
select * from emp emp1 where emp1.sal=(select max(emp2.sal) from emp emp2 where emp2.deptno=emp1.deptno)
·½·¨2.ÏÈ×Ó²éѯ²éÕÒ³ömax sal£¬È»ºóÓëemp±íÏà¹ØÁª£¬Èç¹ûÂß¼¸´ÔÓ»á²úÉú½Ï¶à´úÂë
select * from emp emp1,(select deptno,max(sal) maxsal from emp emp2 group by emp2.deptno) emp3 where emp1.deptno=emp3.deptno and emp1.sal=emp3.maxsal
·½·¨3.ʹÓÃmax·ÖÎöº¯Êý
select deptno,maxsal,empno from(
select max(sal) over (partition by deptno) maxsal,emp.* from emp) emp2
where emp2.sal=emp2.maxsal
·½·¨4.ʹÓÃdense_rank·ÖÎöº¯Êý£¬Èç¹ûÒ»¸ö²¿ÃÅ¿ÉÄÜ´æÔÚ¶à±Ê×î´óнˮ£¬¾Í²»ÄÜʹÓÃrow_number()·ÖÎöº¯Êý
select deptno,sal,empno from(
select emp.*,DENSE_RANK() over (partition by deptno order by sal desc) rownumber from emp) emp2
where rownumber=1
½á¹ûÈçÏ£º
10 5000.00 7839
20 3000.00 7788
20 3000.00 7902
30 2850.00  
Ïà¹ØÎĵµ£º
----start
Ç°Ãæ£¬ÎÒÃǽéÉÜÁË ¾¡Á¿±ÜÃâÔÚSQLÓï¾äµÄWHERE×Ó¾äÖÐʹÓú¯Êý£¬ÒòΪÕâÑù×ö»áʹ¸Ã×Ö¶ÎÉϵÄË÷ÒýʧЧ£¬Ó°ÏìSQLÓï¾äµÄÐÔÄÜ¡£»ùÓÚͬÑùµÄµÀÀí£¬ÎÒÃÇÒ²Ó¦¸Ã±ÜÃâʹÓÃLIKE¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
MYNUMBER VARCHAR(18)---Éí·ÝÖ¤ºÅÂë
);&nb ......
ϵͳ»·¾³£ºwindows xp pro
Êý¾Ý¿â£ºMS Sql 2000
IIS: 5.1 ASP.NET2.0
ÒòΪ´úÂëÊÇ2.0.netµÄ,ûÓа취ֻºÃ°²×°ÁË.netÏÂÔØÄã¿ÉÒÔȥ΢ÈíµÄÍøÕ¾ÉÏÈ¥ÏÂÔØ.Õâ¸öÊÇÃâ·ÑµÄÈí¼þ£¬ÎÒÖªµÀµÄÊÇÃâ·ÑµÄ¡£
Êý¾Ý¿â×öÁËѹËõºó׺ÊÇbakµÄ£¬ËùÒÔÊý¾Ý¿â²»ÄÜÖ±½ÓʹÓã¬ÐèÒªµ¼ÈëÔÚÍøÉϲéÁ˺ܶàµÄ×ÊÁϵ«ÊDz»¶à£¬µ«×Ô¼º³É¹¦µ¼ÈëÁË£¬Ð´ÏÂÀ´×öÒ»¸ö±¸ ......
ÔÚDELPHIÖг£³£ÒªÓõ½ADOQUERYÖеÄSQLÓï¾äÔö¼ÓÐÅÏ¢ºóÖ´ÐУ¬µ±ÒªÇóÔö¼ÓÌõ¼þ£¨Èç²ÎÊýʱ£©ÖмäµÄ¼ä¸ôºÜÖØÒª£¬¸ã²»ºÃ¾ÍÒª³ö´í£ºÏ¾ÙÀý˵Ã÷ÎÒÒª±í´ïµÄÒâ˼£º
È磺һ¸ö±í:student ÓÐ ksh,xm,xb,lqzy,lqcc¼¸¸ö×ֶΣ¬¶¼Îª×Ö·ûÐÍ¡£ÏÖÒªÇó°´xb·Ö×éͳ¼ÆÈËÊý£¬Í¬Ê±lqccÒªÇóÏÞ¶¨Ìõ¼þ¡£
Ò»°ãÇé¿öϵÄSQLÓï¾äÓ¦¸ÃΪ£º select xb,count(*) ......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËÆºõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......