SQL ¶¯Ì¬ÅÅÐòµÄ·½·¨
ÔÚ¹«¹²ÐÂÎÅ×éÖУ¬Ò»¸ö¾³£³öÏÖµÄÎÊÌâÊÇ“ÔõÑù²ÅÄܸù¾Ý´«µÝ¸ø´æ´¢¹ý³ÌµÄ²ÎÊý·µ»ØÒ»¸öÅÅÐòµÄÊä³ö£¿”¡£ÔÚһЩ¸ßˮƽר¼ÒµÄ°ïÖú֮ϣ¬ÎÒÕûÀí³öÁËÕâ¸öÎÊÌâµÄ¼¸ÖÖ½â¾ö·½°¸¡£
Ò»¡¢ÓÃIF...ELSEÖ´ÐÐÔ¤ÏȱàдºÃµÄ²éѯ
¡¡¡¡¶ÔÓÚ´ó¶àÊýÈËÀ´Ëµ£¬Ê×ÏÈÏëµ½µÄ×ö·¨Ò²ÐíÊÇ£ºÍ¨¹ýIF...ELSEÓï¾ä£¬Ö´Ðм¸¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡£ÀýÈ磬¼ÙÉèÒª´ÓNorthwindÊý¾Ý¿â²éѯµÃµ½Ò»¸ö»õÖ÷£¨Shipper£©µÄÅÅÐòÁÐ±í£¬·¢³öµ÷ÓõĴúÂëÒÔ´æ´¢¹ý³Ì²ÎÊýµÄÐÎʽָ¶¨Ò»¸öÁУ¬´æ´¢¹ý³Ì¸ù¾ÝÕâ¸öÁÐÅÅÐòÊä³ö½á¹û¡£Listing 1ÏÔʾÁËÕâÖÖ´æ´¢¹ý³ÌµÄÒ»¸ö¿ÉÄܵÄʵÏÖ£¨GetSortedShippers´æ´¢¹ý³Ì£©¡£
¡¾Listing 1: ÓÃIF...ELSEÖ´Ðжà¸öÔ¤ÏȱàдºÃµÄ²éѯÖеÄÒ»¸ö¡¿
CREATE PROC GetSortedShippers
@OrdSeq AS int
AS
IF @OrdSeq = 1
SELECT * from Shippers ORDER BY ShipperID
ELSE IF @OrdSeq = 2
SELECT * from Shippers ORDER BY CompanyName
ELSE IF @OrdSeq = 3
SELECT * from Shippers ORDER BY Phone
¡¡¡¡ÕâÖÖ·½·¨µÄÓŵãÊÇ´úÂëºÜ¼òµ¥¡¢ºÜÈÝÒ×Àí½â£¬SQL ServerµÄ²éѯÓÅ»¯Æ÷Äܹ»ÎªÃ¿Ò»¸öSELECT²éѯ´´½¨Ò»¸ö²éѯÓÅ»¯¼Æ»®£¬È·±£´úÂë¾ßÓÐ×îÓŵÄÐÔÄÜ¡£ÕâÖÖ·½·¨×îÖ÷ÒªµÄȱµãÊÇ£¬Èç¹û²éѯµÄÒªÇó·¢ÉúÁ˸ı䣬Äã±ØÐëÐ޸Ķà¸ö¶ÀÁ¢µÄSELECT²éѯ——ÔÚÕâÀïÊÇÈý¸ö¡£
¶þ¡¢ÓÃÁÐÃû×Ö×÷Ϊ²ÎÊý
¡¡¡¡ÁíÍâÒ»¸öÑ¡ÔñÊÇÈòéѯÒÔ²ÎÊýµÄÐÎʽ½ÓÊÕÒ»¸öÁÐÃû×Ö¡£Listing 2ÏÔʾÁËÐ޸ĺóµÄGetSortedShippers´æ´¢¹ý³Ì¡£CASE±í´ïʽ¸ù¾Ý½ÓÊÕµ½µÄ²ÎÊý£¬È·¶¨SQL ServerÔÚORDER BY×Ó¾äÖÐʹÓÃÄÄÒ»¸öÁÐÖµ¡£×¢Ò⣬ORDER BY×Ó¾äÖеıí´ïʽ²¢Î´ÔÚSELECTÇåµ¥ÖгöÏÖ¡£ÔÚANSI SQL-92±ê×¼ÖУ¬ORDER BY×Ó¾äÖв»ÔÊÐí³öÏÖûÓÐÔÚSELECTÇåµ¥ÖÐÖ¸¶¨µÄ±í´ïʽ£¬µ«ANSI SQL-99±ê×¼ÔÊÐí¡£SQL ServerÒ»Ö±ÔÊÐíÕâÖÖÓ÷¨¡£
¡¾Listing 2£ºÓÃÁÐÃû×Ö×÷Ϊ²ÎÊý£¬µÚÒ»´Î³¢ÊÔ¡¿
CREATE PROC GetSortedShippers
@ColName AS sysname
AS
SELECT *
from Shippers
ORDER BY
CASE @ColName
WHEN 'ShipperID' THEN ShipperID
WHEN 'CompanyName' THEN CompanyName
WHEN 'Phone' THEN Phone
ELSE NULL
END
¡¡¡¡ÏÖÔÚ£¬ÎÒÃÇÀ´ÊÔÒ»ÏÂеĴ洢¹ý³Ì£¬ÒÔ²ÎÊýµÄÐÎʽָ¶¨ShipperIDÁУº
EXEC GetSortedShippers 'ShipperID'
¡¡¡¡´ËʱһÇÐÕý³£¡£µ«ÊÇ£¬µ±ÎÒÃÇÊÓͼ°ÑCompanyNameÁÐ×÷Ϊ²ÎÊýµ÷Óô洢¹ý³Ìʱ£¬Ëü²»ÔÙÓÐЧ£º
EXEC GetSortedShippers 'CompanyName'
¡¡¡¡×Ðϸ¿´Ò»Ï´íÎóÐÅÏ¢£º
Server
Ïà¹ØÎĵµ£º
''' <summary>
''' SQLÎÄ執ÐÐ結¹û¤ÎEXCEL³öÁ¦
''' </summary>
''' <param name="connString">OLEDB½Ó続ÎÄ×ÖÁÐ</param>
''' <param name="sqlString">SQLÎÄ</param>
''' <param name="savePath">³öÁ¦¥Õ¥¡¥¤¥ë¥Ñ¥¹</param>
......
¡¡¡¡¼ÙÉèÄãÏëÕÒÊéÖеÄijһ¸ö¾ä×Ó¡£Äã¿ÉÒÔÒ»Ò³Ò»Ò³µØÖðÒ³ËÑË÷£¬µ«Õâ»á»¨ºÜ¶àʱ¼ä¡£¶øÍ¨¹ýʹÓÃË÷Òý£¬Äã¿ÉÒÔºÜ¿ìµØÕÒµ½ÄãÒªËÑË÷µÄÖ÷Ìâ¡£
¡¡¡¡±íµÄË÷ÒýÓ븽ÔÚÒ»±¾ÊéºóÃæµÄË÷Òý·Ç³£ÏàËÆ¡£Ëü¿ÉÒÔ¼«´óµØÌá¸ß²éѯµÄËÙ¶È¡£¶ÔÒ»¸ö½Ï´óµÄ±íÀ´Ëµ£¬Í¨¹ý¼ÓË÷Òý£¬Ò»¸öͨ³£Òª»¨·Ñ¼¸¸öСʱÀ´Íê³ÉµÄ²éѯֻҪ¼¸·ÖÖӾͿÉÒÔÍê³É¡£Òò´ËûÓÐÀíÓÉ¶Ô ......
´æ´¢¹ý³Ì´úÂ룺
--drop procedure p_page
--go
create procedure p_page
(
@Tables varchar(1000), --±íÃûÈçtesttable
@PrimaryKey varchar(100),--±íµÄÖ÷¼ü,±ØÐëΨһÐÔ
@Sort varchar(200) = NULL,--ÅÅÐò×Ö¶ÎÈçf_Name asc»òf_name desc(×¢ÒâÖ»ÄÜÓÐÒ»¸öÅÅÐò×Ö¶Î)
@CurrentPage int = 1,--µ± ......
ÉÏÉϸöÐÇÆÚ£¬ÓÐÈË·´À¡£¬CSDNÓÐSQL×¢Èë¶¶´£¬º¹ÑÕ£¬¼¸ÄêǰΪSQL×¢Èë¶¶´£¬²¿ÃÅרÃŶÔËùÓдúÂë×ö¹ýÒ»´Î·Ç³£´óµÄ¼ì²é£¬¾¹È»ÄǴμì²é»¹ÓÐÒÅ©µÄµØ·½¡£×î½üÕ⼸¸öÐÇÆÚ£¬¾ÍÊÇÒ»Ö±ÔÙ¶Ô´úÂë×öÔٴθ´²é£¬¿´ÓÐûÓÐSQL×¢Èë¶¶´¡£
´æÔÚSQL×¢Èë¶¶´£¬¾ÍÒòΪÄãµÄSQLÓï¾äÊÇ×Ô¼ºÆ´´ ......
inner join,full outer join,left join,right jion
ÄÚ²¿Á¬½Ó inner join Á½±í¶¼Âú×ãµÄ×éºÏ
full outer È«Á¬ Á½±íÏàͬµÄ×éºÏÔÚÒ»Æð£¬A±íÓУ¬B±íûÓеÄÊý¾Ý£¨ÏÔʾΪnull£©,ͬÑùB±íÓÐ
A±íûÓеÄÏÔʾΪ(null)
A±í left join B±í ×óÁ¬,ÒÔA±íΪ»ù´¡£¬A±íµÄÈ«²¿Êý¾Ý£¬B±íÓеÄ×éºÏ¡£Ã»ÓеÄΪnull
A±í right join B±í ÓÒÁ ......