SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨Èý£©
ʵÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¡¡¡¡½¨Á¢Ò»¸ö Web Ó¦Ó㬷ÖÒ³ä¯ÀÀ¹¦Äܱز»¿ÉÉÙ¡£Õâ¸öÎÊÌâÊÇÊý¾Ý¿â´¦ÀíÖÐÊ®·Ö³£¼ûµÄÎÊÌâ¡£¾µäµÄÊý¾Ý·ÖÒ³·½·¨ÊÇ:ADO ¼Í¼¼¯·ÖÒ³·¨£¬Ò²¾ÍÊÇÀûÓÃADO×Ô´øµÄ·ÖÒ³¹¦ÄÜ£¨ÀûÓÃÓα꣩À´ÊµÏÖ·ÖÒ³¡£µ«ÕâÖÖ·ÖÒ³·½·¨½öÊÊÓÃÓÚ½ÏСÊý¾ÝÁ¿µÄÇéÐΣ¬ÒòΪÓα걾ÉíÓÐȱµã£ºÓαêÊÇ´æ·ÅÔÚÄÚ´æÖУ¬ºÜ·ÑÄÚ´æ¡£ÓαêÒ»½¨Á¢£¬¾Í½«Ïà¹ØµÄ¼Ç¼Ëøס£¬Ö±µ½È¡ÏûÓαꡣÓαêÌṩÁ˶ÔÌض¨¼¯ºÏÖÐÖðÐÐɨÃèµÄÊֶΣ¬Ò»°ãʹÓÃÓαêÀ´ÖðÐбéÀúÊý¾Ý£¬¸ù¾ÝÈ¡³öÊý¾ÝÌõ¼þµÄ²»Í¬½øÐв»Í¬µÄ²Ù×÷¡£¶ø¶ÔÓÚ¶à±íºÍ´ó±íÖж¨ÒåµÄÓα꣨´óµÄÊý¾Ý¼¯ºÏ£©Ñ»·ºÜÈÝÒ×ʹ³ÌÐò½øÈëÒ»¸öÂþ³¤µÄµÈ´ýÉõÖÁËÀ»ú¡£
¡¡¡¡¸üÖØÒªµÄÊÇ£¬¶ÔÓڷdz£´óµÄÊý¾ÝÄ£ÐͶøÑÔ£¬·ÖÒ³¼ìË÷ʱ£¬Èç¹û°´ÕÕ´«Í³µÄÿ´Î¶¼¼ÓÔØÕû¸öÊý¾ÝÔ´µÄ·½·¨ÊǷdz£ÀË·Ñ×ÊÔ´µÄ¡£ÏÖÔÚÁ÷ÐеķÖÒ³·½·¨Ò»°ãÊǼìË÷Ò³Ãæ´óСµÄ¿éÇøµÄÊý¾Ý£¬¶ø·Ç¼ìË÷ËùÓеÄÊý¾Ý£¬È»ºóµ¥²½Ö´Ðе±Ç°ÐС£
¡¡¡¡×îÔç½ÏºÃµØʵÏÖÕâÖÖ¸ù¾ÝÒ³Ãæ´óСºÍÒ³ÂëÀ´ÌáÈ¡Êý¾ÝµÄ·½·¨´ó¸Å¾ÍÊÇ“¶íÂÞ˹´æ´¢¹ý³Ì”¡£Õâ¸ö´æ´¢¹ý³ÌÓÃÁËÓα꣬ÓÉÓÚÓαêµÄ¾ÖÏÞÐÔ£¬ËùÒÔÕâ¸ö·½·¨²¢Ã»Óеõ½´ó¼ÒµÄÆÕ±éÈÏ¿É¡£
¡¡¡¡ºóÀ´£¬ÍøÉÏÓÐÈ˸ÄÔìÁË´Ë´æ´¢¹ý³Ì£¬ÏÂÃæµÄ´æ´¢¹ý³Ì¾ÍÊǽáºÏÎÒÃǵİ칫×Ô¶¯»¯ÊµÀýдµÄ·ÖÒ³´æ´¢¹ý³Ì£º
CREATE procedure pagination1
(@pagesize int, --Ò³Ãæ´óС£¬Èçÿҳ´æ´¢20Ìõ¼Ç¼
@pageindex int --µ±Ç°Ò³Âë
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int) --¶¨Òå±í±äÁ¿
declare @PageLowerBound int --¶¨Òå´ËÒ³µÄµ×Âë
declare @PageUpperBound int --¶¨Òå´ËÒ³µÄ¶¥Âë
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select gid from TGongwen
¡¡¡¡¡¡¡¡¡¡¡¡where fariqi >dateadd(day,-365,getdate()) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t
where O.gid=t.nid and t.id>@PageLowerBound
and t.id<=@PageUpperBound order by t.id
end
set nocount off
¡¡¡¡ÒÔÉÏ´æ´¢¹ý³ÌÔËÓÃÁËSQL SERVERµÄ×îм¼Êõ¨D¨D±í±äÁ¿¡£Ó¦¸Ã˵Õâ¸ö´æ´¢¹ý³ÌÒ²ÊÇÒ»¸ö·Ç³£ÓÅÐãµÄ·ÖÒ³´æ´¢¹ý³Ì¡£µ±È»£¬ÔÚÕâ¸ö¹ý³ÌÖУ¬ÄúÒ²¿ÉÒÔ°ÑÆäÖеıí±äÁ¿Ð´³ÉÁÙʱ±í£ºCREATE TABLE #Temp¡£µ«ºÜÃ÷ÏÔ£
Ïà¹ØÎĵµ£º
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£Õ ......
Ò»¡¢NATURAL JOIN£¨×ÔÈ»Á¬½Ó£©
Á½Õűíͨ¹ýNATURAL JOINÁ¬½ÓµÄʱºò£¬Ï൱ÓÚÓиöÒþº¬µÄWHERE×Ӿ䣬¶ÔÁ½ÕűíÖÐͬÃûµÄ¶ÔÓ¦ÁÐÏà±È½Ï¿´ÊÇ·ñÏàµÈ¡£
¶þ¡¢CROSS JOIN£¨´´½¨µÑ¿¨¶û»ý£©
¶ÔÁ½Õűíͨ¹ý½»²æÁªºÏ²úÉúµÚÈýÕÅ·µ»Ø½á¹û¼¯µÄ±í¡£Ï൱ÓÚÆÕͨµÄÁ¬½Ó¡£
Èý¡¢INNER JOIN£ ......
ÏÖÓÐtable_for_report_1ºÍtable_for_report_2£¬ÏêÇéÈçÏ£º
table_for_report_1ÓÐnum×ֶΣ¬c1,c2,c3×ֶΡ£
Êý¾ÝÈçÏ£º
num c1 c2 c3
1 15001346690 11 12 13
2 1 ......
ÔÚ±¾ÎÄÖУ¬GetDate()»ñµÃµÄÈÕÆÚÓÉÁ½²¿·Ö×é³É£¬·Ö±ðÊǽñÌìµÄÈÕÆں͵±Ê±µÄʱ¼ä£ºSelect GetDate() ÓÃDateName()¾Í¿ÉÒÔ»ñµÃÏàÓ¦µÄÄê¡¢Ô¡¢ÈÕ£¬È»ºóÔÙ°ÑËüÃÇÁ¬½ÓÆðÀ´¾Í¿ÉÒÔÁË£º
¡¡¡¡
Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Dat ......
×÷Õß:ÂÞ´ú¾ù ldj_work#126.com תÔØÇë±£³ÖÍêÕûÐÔ
1.²âÊÔ±í
employee
¹ÍÔ±id ²¿ÃÅid н½ð
emp_id dept_id salary
......