Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

PL/SQLʶȤ

×÷Õß: ÈýÊ®¶øÁ¢Ê±¼ä£º2009Äê10ÔÂ15ÈÕ 19:21:13±¾Îijö×Ô ¡°inthirties£¨ÈýÊ®¶øÁ¢£©¡±²©¿Í£¬×ªÔØÇëÎñ±Ø×¢Ã÷×÷Õߺͱ£Áô³ö´¦http://blog.csdn.net/inthirties/archive/2009/10/15/4673331.aspx ѧϰÊÇ¿ÝÔïµÄ£¬ËùÒÔ×÷Ϊһ¸öѧϰÕߣ¬ÒªÑ§»áÔÚѧϰÖÐÕÒµ½¿ìÀÖ£¬ÕâÑù²ÅÄܼ¤·¢ÐËȤ£¬ÐËȤÊÇ×îºÃµÄÀÏʦ£¬ÕâÑù£¬Ñ§Ï°¾ÍÂýÂýµÄ±ä³ÉÁËÒ»¼þ¿ìÀÖµÄÊÂÇéß·¡£Ô­±¾Ò»Ö±×öOracleµÄ¹ÜÀíºÍά»¤£¬¶ÔPLSQL²»ÊǺܸÐÐËȤ£¬ÔÚCSDNÀï¾­³£ÓÐЩÅóÓÑÎʵ½¿ª·¢µÄÎÊÌ⣬¿ªÊ¼ÊÇͨ¹ýgoogleÕÒµ½´ð°¸£¬ºóÀ´ÔÚÕÒÑ°µÄ¹ý³ÌÖУ¬Ñ§Ï°µ½ÁË֪ʶµã£¬Ò²¾ÍÂýÂýµÄ¿ªÊ¼°ïÖú±ðÈ˽øÐÐsqlµÄ¿ª·¢°ïÖú£¬ÕâÖÖ°ïÖúÒ²¸øÎÒÁËѧϰµÄ¶¯Á¦£¬ËùÒÔPLSQLµÄ¿ª·¢µÄ»ù±¾¶«Î÷Ò²¾ÍÕÆÎÕÁ˲»ÉÙÁË£¬¶øÇÒÓÉÓÚÊÇ¿ìÀÖµÄѧϰ£¬ËùÒÔÒ²±È½ÏÇáËÉ£¬ÕâÀï¾ÍÊÇÓøöÒ»¸öÍøÓѵÄÎÊÌâµÄÍêÕû½âÌâ¹ý³ÌÀ´¿´¿´ÎÒµ±Ê±¶ÔOverµÄѧϰ¡£ ÕâÊÇÒ»¸ö±È½ÏÓÐȤµÄSQLÌ⣬ 
ÓÐÒ»¸ö±í£¬½á¹¹ÈçÏ£º 
±àºÅ£¬ÐÕÃû£¬Ê±¼ä£¬»ù±¾¹¤×Ê£¬½±½ð£¬¿Û¿î 
1 ÕÅÈý 8-20 1000 1000 5 
2 ÀîËÄ 8-20 1100 900 10 
3 ÕÅÈý 9-20 900 1000 10 
4 ÀîËÄ 9-20 1100 900 13 
5 ÍõÎå 9-20 900 100 0  Èç¹ûÎÒ¶Ô8-20 ºÍ 9-20 µÄ»ù±¾¹¤×Ê×öÒ춯Êý¾Ý²éѯ ÎÒÒªÇóµÃµ½ÕâÑùµÄÒ»¸öÁÙʱÊý¾ÝÊÓͼ 

±àºÅ£¬ÐÕÃû£¬»ù±¾¹¤×Ê ±¸×¢ 
1 ÕÅÈý -100 
2 ÍõÎå 900 ÐÂÔö 
Ô­ÌûÔÚhttp://topic.csdn.net/u/20090830/20/4f135d40-a52d-4346-ac15-599b2951af39.html?seed=709316630&r=59447116#r_59447116  

Óöµ½ÓÐȤµÄÎÊÌ⣬һϾͼ¤·¢ÎÒµÄÐËȤ£¬¿´ÕâÑùÐèÒªÉÏϼǼ¶ÔÕյģ¬¾ÍÏëµ½lagºÍleadº¯ÊýÁË lagºÍleadµÄ¹¦ÄÜÊÇOracleÊý¾Ý¿â¶ÀÓеŦÄÜ£¬ÊÇ·Ö×麯ÊýÖеÄÒ»¸öºÜÓмÛÖµµÄ·½·¨¡£

ÕâÀïÒ²½èÕâ¸öÌ⣬ºÃºÃµÄ½âÊÍÒ»ÏÂlagºÍleadµÄÓ÷¨ 

Lag È¡Ç°ÃænÌõµÄ¼Ç¼ 
lag(field, n, defaultv)  ÕâÀïfieldÊÇÎÒÃÇÐèҪȡµÄ×ֶΣ¬ nÊÇÈ¡¶àÉÙ¸ö£¬defaultVÊÇÈ¡²»µ½µÄĬÈÏÖµ¡£

LeadÈ¡ºóÃæÄÇÌõµÄ¼Ç¼ 
lead(field, n, defaultv)  ºÍlagÒ»ÑùµÄ£¬fieldÊÇÈ¡ÖµµÄ×ֶΣ¬ nÊÇÇ°ºó¼¸Ìõ£¬ defualtvÊÇĬÈÏÖµ£¬ÀàËÆnvl(lag(field, n), defaultv) 

lagºÍlead±ØÐëºÍ¿ª´°º¯ÊýoverÒ»ÆðÀ´Ê¹Ó㬠

ÄÇô֪µÀOracleÀïÓÐÕâÑùʵÓõŦÄÜ£¬ÄÇôÎÒÃÇÈçºÎ¾ßÌåÀ´Ê¹ÓÃlag/leadºÍover£¬ÊµÕ½Ê¤ÓÚÒ»ÇР

ÏÈ×öÊý¾Ý 
ÏÖÔÚÏ°¹ßÓÃwith asÀ´×öÊý¾ÝÁË 

with temp as


Ïà¹ØÎĵµ£º

ʹÓÃSQLServerÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾ä

Èç¹ûÄã¾­³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾­³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ­¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......

SQL ServerÊý¾Ý¿â¿ª·¢µÄ¶þʮһÌõ

Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾­Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ ......

SQL×¢Èë¹¥»÷µÄÖÖÀàºÍ·À·¶ÊÖ¶Î

¹Û²ì½üÀ´µÄһЩ°²È«Ê¼þ¼°Æäºó¹û£¬°²È«×¨¼ÒÃÇÒѾ­µÃµ½Ò»¸ö½áÂÛ£¬ÕâЩÍþвÖ÷ÒªÊÇͨ¹ýSQL×¢ÈëÔì³ÉµÄ¡£ËäȻǰÃæÓÐÐí¶àÎÄÕÂÌÖÂÛÁËSQL×¢È룬µ«½ñÌìËùÌÖÂÛµÄÄÚÈÝÒ²Ðí¿É°ïÖúÄã¼ì²é×Ô¼ºµÄ·þÎñÆ÷£¬²¢²ÉÈ¡ÏàÓ¦·À·¶´ëÊ©¡£
SQL×¢Èë¹¥»÷µÄÖÖÀà
Öª±ËÖª¼º£¬·½¿Éȡʤ¡£Ê×ÏÈÒªÇå³þSQL×¢Èë¹¥»÷ÓÐÄÄЩÖÖÀà¡£
1.ûÓÐÕýÈ·¹ýÂËתÒå× ......

SQL Server¶¨Ê±×÷Òµ

Èç¹ûÔÚSQL Server
ÀïÐèÒª¶¨Ê±»òÕßÿ¸ôÒ»¶Îʱ¼äÖ´ÐÐij¸ö´æ´¢¹ý³Ì»ò3200×Ö·ûÒÔÄÚµÄSQLÓï¾äʱ,¿ÉÒÔÓùÜÀí->SQL Server´úÀí->×÷ÒµÀ´ÊµÏÖ¡£
¡¡¡¡1¡¢¹ÜÀí->SQL Server´úÀí->×÷Òµ(°´Êó±êÓÒ¼ü)->н¨×÷Òµ->
¡¡¡¡2¡¢Ð½¨×÷ÒµÊôÐÔ(³£¹æ)->Ãû³Æ[×Ô¶¨Òå±¾´Î×÷ÒµµÄÃû³Æ]->Æô
Óõķ½¿òÄÚÊǹ´ºÅ->·ÖÀà´¦¿É ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ