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
Ïà¹ØÎĵµ£º
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ ......
¹Û²ì½üÀ´µÄһЩ°²È«Ê¼þ¼°Æäºó¹û£¬°²È«×¨¼ÒÃÇÒѾµÃµ½Ò»¸ö½áÂÛ£¬ÕâЩÍþвÖ÷ÒªÊÇͨ¹ýSQL×¢ÈëÔì³ÉµÄ¡£ËäȻǰÃæÓÐÐí¶àÎÄÕÂÌÖÂÛÁËSQL×¢È룬µ«½ñÌìËùÌÖÂÛµÄÄÚÈÝÒ²Ðí¿É°ïÖúÄã¼ì²é×Ô¼ºµÄ·þÎñÆ÷£¬²¢²ÉÈ¡ÏàÓ¦·À·¶´ëÊ©¡£
SQL×¢Èë¹¥»÷µÄÖÖÀà
Öª±ËÖª¼º£¬·½¿Éȡʤ¡£Ê×ÏÈÒªÇå³þSQL×¢Èë¹¥»÷ÓÐÄÄЩÖÖÀà¡£
1.ûÓÐÕýÈ·¹ýÂËתÒå× ......
Èç¹ûÔÚSQL Server
ÀïÐèÒª¶¨Ê±»òÕßÿ¸ôÒ»¶Îʱ¼äÖ´ÐÐij¸ö´æ´¢¹ý³Ì»ò3200×Ö·ûÒÔÄÚµÄSQLÓï¾äʱ,¿ÉÒÔÓùÜÀí->SQL Server´úÀí->×÷ÒµÀ´ÊµÏÖ¡£
¡¡¡¡1¡¢¹ÜÀí->SQL Server´úÀí->×÷Òµ(°´Êó±êÓÒ¼ü)->н¨×÷Òµ->
¡¡¡¡2¡¢Ð½¨×÷ÒµÊôÐÔ(³£¹æ)->Ãû³Æ[×Ô¶¨Òå±¾´Î×÷ÒµµÄÃû³Æ]->Æô
Óõķ½¿òÄÚÊǹ´ºÅ->·ÖÀà´¦¿É ......