[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
ÓÐʱºò±¨±íÉÏÃæÐèÒªÏÔʾ¸Ã±Ê²Ù×÷µÄÉÏÒ»²½Öè»òÕßÏÂÒ»²½ÖèµÄÏêϸÐÅÏ¢£¬Õâ¸öʱºò¿ÉÒÔ°´ÕÕÏÂÃæµÄ×ö·¨£º
ÏÈ´´½¨Ê¾Àý±í£º
-- Create table
create table LEAD_TABLE
(
CASEID VARCHAR2(10),
STEPID VARCHAR2(10),
ACTIONDATE DATE
)
tablespace COLM_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case1','Step1',to_date('20070201','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd'));
insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd'));
commit;
ÿһÌõ¼Ç¼¶¼ÄÜÁ¬½Óµ½ÉÏ/ÏÂÒ»ÐеÄÄÚÈÝ
select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by&
Ïà¹ØÎĵµ£º
´óÅúÁ¿²åÈëÊý¾Ýʱ 1,myisam±í,¿ÉÒÔÏÈͨ¹ý alter table table_name disable keys;#ÏȹرձíµÄË÷Òý¼ì²é,×¢ÒâÊÇ·ÇΨһË÷Òý! load data infile ¡®/path/file¡¯ into table table_name; alter table table_name anable keys;#ÔÙ´ò¿ªË÷Òý ¿É´ó´ó¼Ó¿ìµ¼Èë.»¹ÓпÉÒÔÉèÖÃbulk_insert_buffer_sizeÖµÀ´Ìá¸ß²åÈëËÙ¶È ......
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()
ʵ¼ÊÓ¦ÓÃÖÐÎÒÃÇ¿ÉÒÔͨ¹ýsum()ͳ¼Æ³ö×éÖеÄ×ܼƻòÕßÊÇÀÛ¼ÓÖµ£¬¾ßÌåʾÀýÈçÏ£º
......
±¾ÏµÁÐÎÄÕµ¼º½
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Ò»)--sum()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(¶þ)--max()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(Èý)--row_number() /rank()/dense_rank()
[Oracle]¸ßЧµÄSQLÓï¾äÖ®·ÖÎöº¯Êý(ËÄ)--lag()/lead()
Èç¹ûÎÒÃÇ°´ÕÕʾÀýÏëµÃµ½Ã¿¸ö²¿ÃÅнˮֵ×î¸ßµÄ¹ÍÔ±µÄ¼Í¼£¬¿ÉÒÔÓÐËÄÖÖ·½·¨ÊµÏÖ£ ......