[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐЩʱºòÎÒÃÇÏ£ÍûµÃµ½Ö¸¶¨Êý¾ÝÖеÄÇ°nÁУ¬Ê¾ÀýÈçÏ£º
µÃµ½Ã¿¸ö²¿ÃÅнˮ×î¸ßµÄÈý¸ö¹ÍÔ±£º
ÏÈ´´½¨Ê¾Àý±í
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);
ÏÈ¿´Ò»ÏÂrow_number() /rank()/dense_rank()Èý¸öº¯ÊýÖ®¼äµÄÇø±ð
select emp.deptno,emp.sal,emp.empno,row_number() over (partition by deptno order by sal desc) row_number, --1,2,3
rank() over (partition by deptno order by sal desc) rank, --1,1,3
dense_rank() over (partition by deptno order by sal desc) dense_rank from emp --1,1,2
½á¹ûÈçÏÂ:
10 5000.00 7839 1 1 1
10 2450.00 7782 2 2 2
10 1300.00 7934 3 3 3
20 3000.00 7788 1 1 1
20 3000.00 7902 2 1 1
20 2975.00 7566 3 3 &n
Ïà¹ØÎĵµ£º
--1.ÔÚÄ¿±ê·þÎñÆ÷ÉϽ¨Á¢Èç϶ÔÏó(±»Í¬²½µÄ·þÎñÆ÷)
if exists (select * from dbo.sysobjects where id = object_id(N'[sys_syscomments_bak]') and OBJECTPROPERTY(id, N'IsUserTable')   ......
/*
ʹÓÃÊÂÎñÈÕÖ¾»Ö¸´Êý¾ÝµÄʵÑé
*/
--1.´´½¨²âÊÔÊý¾Ý¿â
CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA,
FILENAME = 'c:\Db_test.mdf' )
LOG ON
( NAME = Db_test_LOG,
FILENAME = 'c:\Db_test.ldf')
GO
--2.¹ÊÕÏ»¹ÔÄ£ÐÍÉèÖÃΪfullÄ£ÐÍ,ĬÈϵļòµ¥Ä£Ðͱ¸·Ý ......
SQL×¢Èë¼òµ¥·ÖÎö
ʾÀýÓï¾ä£º
select * from admintable where adminName like '%a%'
ÔÚ²éѯÖÐÎÒÃÇÒ»°ãÔÚaÕâ¸öµØ·½ÓɽçÃæ´«È벻ͬµÄÖµ£¬µ±ÎÒÃÇÔÚaÕâÀï´«ÈëµÄֵΪ“'”µ¥ÒýºÅʱ£¬Æ´´Õ³ÉµÄSQLÓï¾ä¾ÍÈçÏ£º
select * from admintable where adminName like '%'%'
Ö´ÐÐÕâ¾äÓï¾äÎÒÃǻᷢÏÖ³öÏÖÒÔÏÂÒì³££º
......
2008µÄSSMS±È2005°æÒª¶àÏûºÄÒ»±¶×óÓÒµÄÄڴ棬¶øÇÒËƺõ²»»á×Ô¼ºÊÍ·Å£¬ÖÁÉÙÒ²ÊÇÄÚ´æ¹ÜÀí²»ÊǺܺÏÀí£¬ÍùÍù´ò¿ª¼¸¸ö²éѯ´°¿Ú½øÐвéѯºóÄÚ´æ¾Í»áÉýµ½ÄÑÒÔ200MBµ½300MB£¬ÇҹصôºóÄÚ´æ²»»áÊÍ·Å£¬¶ø2005µÄSSMSÒ»°ãÖ»ÊÇÔÚ100MB×óÓÒ¡£¶ÔÓµÓдóÄÚ´æµÄµçÄÔÀ´ËµÕâ¿ÉÄܲ»Ëãʲô£¬µ«¶ÔÄÚ´æÖ»ÓÐ1G»ò¸üÉÙµÄÓû§À´Ëµ£¬Õ⼸ºõÊDz»¿ÉÈÝÈ̵ģ¬ÒòÎ ......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
Èç¹ûÎÒÃÇ°´ÕÕʾÀýÏëµÃµ½Ã¿¸ö²¿ÃÅнˮֵ×î¸ßµÄ¹ÍÔ±µÄ¼Í¼£¬¿ÉÒÔÓÐËÄÖÖ·½·¨ÊµÏÖ£ ......