ÔÚÒÔϵÄÎÄÕÂÖУ¬ÎÒ½«ÒÔ“°ì¹«×Ô¶¯»¯”ϵͳΪÀý£¬Ì½ÌÖÈçºÎÔÚÓÐ×Å1000ÍòÌõÊý¾ÝµÄMS SQL SERVERÊý¾Ý¿âÖÐʵÏÖ¿ìËÙµÄÊý¾ÝÌáÈ¡ºÍÊý¾Ý·ÖÒ³¡£ÒÔÏ´úÂë˵Ã÷ÁËÎÒÃÇʵÀýÖÐÊý¾Ý¿âµÄ“ºìÍ·Îļþ”Ò»±íµÄ²¿·ÖÊý¾Ý½á¹¹£º
CREATE TABLE [dbo].[TGongwen] ( --TGongwenÊǺìÍ·Îļþ±íÃû
[Gid] [int] IDENTITY (1, 1) NOT NULL ,
--±¾±íµÄidºÅ£¬Ò²ÊÇÖ÷¼ü
[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
--ºìÍ·ÎļþµÄ±êÌâ
[fariqi] [datetime] NULL ,
--·¢²¼ÈÕÆÚ
[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--·¢²¼Óû§
[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,
--ÐèÒªä¯ÀÀµÄÓû§¡£Ã¿¸öÓû§ÖмäÓ÷ָô·û“,”·Ö¿ª
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ÏÂÃ棬ÎÒÃÇÀ´ÍùÊý¾Ý¿âÖÐÌí¼Ó1000ÍòÌõÊý¾Ý£º
declare @i int
set @i=1
while @i<=250000
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','ͨÐÅ¿Æ','ͨÐÅ¿Æ,°ì¹«ÊÒ,Íõ¾Ö³¤,Áõ¾Ö³¤,Õžֳ¤,admin,ÐÌÕìÖ§¶Ó,ÌØÇÚÖ§¶Ó,½»Ñ²¾¯Ö§¶Ó,¾ÕìÖ§¶Ó,»§Õþ¿Æ,Öΰ²Ö§¶Ó,ÍâÊ¿Æ','ÕâÊÇ×îÏȵÄ25ÍòÌõ¼Ç¼')
set @i=@i+1
end
GO
declare @i int
set @i=1
while @i<=250000
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','°ì¹«ÊÒ','°ì¹«ÊÒ,ͨÐÅ¿Æ,Íõ¾Ö³¤,Áõ¾Ö³¤,Õžֳ¤,admin,ÐÌÕìÖ§¶Ó,ÌØÇÚÖ§¶Ó,½»Ñ²¾¯Ö§¶Ó,¾ÕìÖ§¶Ó,»§Õþ¿Æ,ÍâÊ¿Æ','ÕâÊÇÖмäµÄ25ÍòÌõ¼Ç¼')
set @i=@i+1
end
GO
declare @h int
set @h=1
while @h<=100
begin
declare @i int
set @i=2002
while @i<=2003
begin
declare @j int
set @j=0
while @j<50
begin
declare @k int
set @k=0
while @k<50
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'ͨÐÅ¿Æ','°ì¹«ÊÒ,ͨÐÅ¿Æ,Íõ¾Ö³¤,Áõ¾Ö³¤,Õžֳ¤,admin,ÐÌÕìÖ§¶Ó,ÌØÇÚÖ§¶Ó,½»Ñ²¾¯Ö§¶Ó,¾ÕìÖ§¶Ó,»§Õþ¿Æ,ÍâÊ¿Æ','ÕâÊÇ×îºóµÄ50ÍòÌõ¼Ç¼')
set @k=@k+1
end
set @j=@j+1
end
set @i=@i+1
end
set @h=@h+1
end
GO
declare @i int
set @i=1
while @i<=9000000
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','ͨÐÅ¿Æ','ͨÐÅ¿Æ,°ì¹«ÊÒ,Íõ¾Ö³¤,Áõ¾Ö³¤,Õžֳ¤,admin,ÐÌÕìÖ§¶Ó,ÌØÇÚÖ§¶Ó,½»Ñ²¾¯Ö§¶Ó,¾ÕìÖ§¶Ó,»§Õþ¿Æ,Öΰ²Ö§¶Ó,ÍâÊ¿Æ','ÕâÊÇ×îºóÌí¼ÓµÄ900ÍòÌõ¼Ç¼')
set @i=@i+1000000
end
GO
ͨ¹ýÒÔÉÏÓï¾ä£¬ÎÒÃÇ´´½¨ÁË25ÍòÌõÓÉͨÐÅ¿ÆÓÚ2004Äê2ÔÂ5ÈÕ·¢²¼µÄ¼Ç¼£¬25ÍòÌõÓɰ칫ÊÒÓÚ2004Äê9ÔÂ6ÈÕ·¢²¼µÄ¼Ç¼£¬2002ÄêºÍ2003Äê¸÷100¸ö2500ÌõÏàͬÈÕÆÚ¡¢²»Í¬·ÖÃëµÄÓÉͨÐÅ¿Æ·¢²¼µÄ¼Ç¼£¨¹²50ÍòÌõ£©£¬»¹ÓÐÓÉͨÐÅ¿ÆÓÚ2004Äê5ÔÂ5ÈÕ·¢²¼µÄ900ÍòÌõ¼Ç¼£¬ºÏ¼Æ1000ÍòÌõ¡£
Ò»¡¢ÒòÇéÖÆÒË£¬½¨Á¢“Êʵ±”µÄË÷Òý
½¨Á¢“Êʵ±”µÄË÷ÒýÊÇʵÏÖ²éѯÓÅ»¯µÄÊ×ҪǰÌá¡£
Ë÷Òý£¨index£©Êdzý±íÖ®ÍâÁíÒ»ÖØÒªµÄ¡¢Óû§¶¨ÒåµÄ´æ´¢ÔÚÎïÀí½éÖÊÉϵÄÊý¾Ý½á¹¹¡£µ±¸ù¾ÝË÷ÒýÂëµÄÖµËÑË÷Êý¾Ýʱ£¬Ë÷ÒýÌṩÁ˶ÔÊý¾ÝµÄ¿ìËÙ·ÃÎÊ¡£ÊÂʵÉÏ£¬Ã»ÓÐË÷Òý,Êý¾Ý¿âÒ²Äܸù¾ÝSELECTÓï¾ä³É¹¦µØ¼ìË÷µ½½á¹û£¬µ«Ëæ×űí±äµÃÔ½À´Ô½´ó£¬Ê¹ÓÓÊʵ±”µÄË÷ÒýµÄЧ¹û¾ÍÔ½À´Ô½Ã÷ÏÔ¡£×¢Ò⣬ÔÚÕâ¾ä»°ÖУ¬ÎÒÃÇÓÃÁË“Êʵ±”Õâ¸ö´Ê£¬ÕâÊÇÒòΪ£¬Èç¹ûʹÓÃË÷Òýʱ²»ÈÏÕ濼ÂÇÆäʵÏÖ¹ý³Ì£¬Ë÷Òý¼È¿ÉÒÔÌá¸ßÒ²»áÆÆ»µÊý¾Ý¿âµÄ¹¤×÷ÐÔÄÜ¡£
£¨Ò»£©ÉîÈëdz³öÀí½âË÷Òý½á¹¹
ʵ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
Æäʵ£¬ÎÒÃǵĺºÓï×ÖµäµÄÕýÎı¾Éí¾ÍÊÇÒ»¸ö¾Û¼¯Ë÷Òý¡£±ÈÈ磬ÎÒÃÇÒª²é“°²”×Ö£¬¾Í»áºÜ×ÔÈ»µØ·¿ª×ÖµäµÄÇ°¼¸Ò³£¬ÒòΪ“°²”µÄÆ´ÒôÊÇ“an”£¬¶ø°´ÕÕÆ´ÒôÅÅÐòºº×ÖµÄ×ÖµäÊÇÒÔÓ¢ÎÄ×Öĸ“a”¿ªÍ·²¢ÒÔ“z”½áβµÄ£¬ÄÇô“°²”×Ö¾Í×ÔÈ»µØÅÅÔÚ×ÖµäµÄÇ°²¿¡£Èç¹ûÄú·ÍêÁËËùÓÐÒÔ“a”¿ªÍ·µÄ²¿·ÖÈÔÈ»ÕÒ²»µ½Õâ¸ö×Ö£¬ÄÇô¾Í˵Ã÷ÄúµÄ×ÖµäÖÐûÓÐÕâ¸ö×Ö£»Í¬ÑùµÄ£¬Èç¹û²é“ÕÅ”×Ö£¬ÄÇÄúÒ²»á½«ÄúµÄ×ֵ䷵½×îºó²¿·Ö£¬ÒòΪ“ÕÅ”µÄÆ´ÒôÊÇ“zhang”¡£Ò²¾ÍÊÇ˵£¬×ÖµäµÄÕýÎIJ¿·Ö±¾Éí¾ÍÊÇÒ»¸öĿ¼£¬Äú²»ÐèÒªÔÙÈ¥²éÆäËûĿ¼À´ÕÒµ½ÄúÐèÒªÕÒµÄÄÚÈÝ¡£
ÎÒÃÇ°ÑÕâÖÖÕýÎÄÄÚÈݱ¾Éí¾ÍÊÇÒ»ÖÖ°´ÕÕÒ»¶¨¹æÔòÅÅÁеÄĿ¼³ÆΪ“¾Û¼¯Ë÷Òý”¡£
Èç¹ûÄúÈÏʶij¸ö×Ö£¬Äú¿ÉÒÔ¿ìËٵشÓ×Ô¶¯Öв鵽Õâ¸ö×Ö¡£µ«ÄúÒ²¿ÉÄÜ»áÓöµ½Äú²»ÈÏʶµÄ×Ö£¬²»ÖªµÀËüµÄ·¢Òô£¬Õâʱºò£¬Äú¾Í²»ÄÜ°´Õողŵķ½·¨ÕÒµ½ÄúÒª²éµÄ×Ö£¬¶øÐèҪȥ¸ù¾Ý“Æ«ÅÔ²¿Ê×”²éµ½ÄúÒªÕÒµÄ×Ö£¬È»ºó¸ù¾ÝÕâ¸ö×ÖºóµÄÒ³ÂëÖ±½Ó·µ½Ä³Ò³À´ÕÒµ½ÄúÒªÕÒµÄ×Ö¡£µ«Äú½áºÏ“²¿Ê×Ŀ¼”ºÍ“¼ì×ֱ픶ø²éµ½µÄ×ÖµÄÅÅÐò²¢²»ÊÇÕæÕýµÄÕýÎĵÄÅÅÐò·½·¨£¬±ÈÈçÄú²é“ÕÅ”×Ö£¬ÎÒÃÇ¿ÉÒÔ¿´µ½Ôڲ鲿Ê×Ö®ºóµÄ¼ì×Ö±íÖГÕÅ”µÄÒ³ÂëÊÇ672Ò³£¬¼ì×Ö±íÖГÕÅ”µÄÉÏÃæÊÇ“³Û”×Ö£¬µ«Ò³ÂëÈ´ÊÇ63Ò³£¬“ÕÅ”µÄÏÂÃæÊÇ“åó”×Ö£¬Ò³ÃæÊÇ390Ò³¡£ºÜÏÔÈ»£¬ÕâЩ×Ö²¢²»ÊÇÕæÕýµÄ·Ö±ðλÓÚ“ÕÅ”×ÖµÄÉÏÏ·½£¬ÏÖÔÚÄú¿´µ½µÄÁ¬ÐøµÄ“³Û¡¢ÕÅ¡¢åó”Èý×Öʵ¼ÊÉϾÍÊÇËûÃÇÔڷǾۼ¯Ë÷ÒýÖеÄÅÅÐò£¬ÊÇ×ÖµäÕýÎÄÖеÄ×ÖÔڷǾۼ¯Ë÷ÒýÖеÄÓ³Éä¡£ÎÒÃÇ¿ÉÒÔͨ¹ýÕâÖÖ·½Ê½À´ÕÒµ½ÄúËùÐèÒªµÄ×Ö£¬µ«ËüÐèÒªÁ½¸ö¹ý³Ì£¬ÏÈÕÒµ½Ä¿Â¼ÖеĽá¹û£¬È»ºóÔÙ·µ½ÄúËùÐèÒªµÄÒ³Âë¡£
ÎÒÃÇ°ÑÕâÖÖĿ¼´¿´âÊÇĿ¼£¬ÕýÎÄ´¿´âÊÇÕýÎĵÄÅÅÐò·½Ê½³ÆΪ“·Ç¾Û¼¯Ë÷Òý”¡£
ͨ¹ýÒÔÉÏÀý×Ó£¬ÎÒÃÇ¿ÉÒÔÀí½âµ½Ê²Ã´ÊÇ“¾Û¼¯Ë÷Òý”ºÍ“·Ç¾Û¼¯Ë÷Òý”¡£
½øÒ»²½ÒýÉêһϣ¬ÎÒÃÇ¿ÉÒÔºÜÈÝÒ×µÄÀí½â£ºÃ¿¸ö±íÖ»ÄÜÓÐÒ»¸ö¾Û¼¯Ë÷Òý£¬ÒòΪĿ¼ֻÄÜ°´ÕÕÒ»ÖÖ·½·¨½øÐÐÅÅÐò¡£
£¨¶þ£©ºÎʱʹÓþۼ¯Ë÷Òý»ò·Ç¾Û¼¯Ë÷Òý
ÏÂÃæµÄ±í×ܽáÁ˺ÎʱʹÓþۼ¯Ë÷Òý»ò·Ç¾Û¼¯Ë÷Òý£¨ºÜÖØÒª£©¡£
¶¯×÷ÃèÊö
ʹÓþۼ¯Ë÷Òý
ʹÓ÷Ǿۼ¯Ë÷Òý
Áо³£±»·Ö×éÅÅÐò
Ó¦
Ó¦
·µ»Øij·¶Î§ÄÚµÄÊý¾Ý
Ó¦
²»Ó¦
Ò»¸ö»ò¼«ÉÙ²»Í¬Öµ
²»Ó¦
²»Ó¦
СÊýÄ¿µÄ²»Í¬Öµ
Ó¦
²»Ó¦
´óÊýÄ¿µÄ²»Í¬Öµ
²»Ó¦
Ó¦
Ƶ·±¸üеÄÁÐ
²»Ó¦
Ó¦
Íâ¼üÁÐ
Ó¦
Ó¦
Ö÷¼üÁÐ
Ó¦
Ó¦
Ƶ·±ÐÞ¸ÄË÷ÒýÁÐ
²»Ó¦
Ó¦
ÊÂʵÉÏ£¬ÎÒÃÇ¿ÉÒÔͨ¹ýÇ°Ãæ¾Û¼¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄ¶¨ÒåµÄÀý×ÓÀ´Àí½âÉÏ±í¡£È磺·µ»Øij·¶Î§ÄÚµÄÊý¾ÝÒ»Ïî¡£±ÈÈçÄúµÄij¸ö±íÓÐÒ»¸öʱ¼äÁУ¬Ç¡ºÃÄú°Ñ¾ÛºÏË÷Òý½¨Á¢ÔÚÁ˸ÃÁУ¬ÕâʱÄú²éѯ2004Äê1ÔÂ1ÈÕÖÁ2004Äê10ÔÂ1ÈÕÖ®¼äµÄÈ«²¿Êý¾Ýʱ£¬Õâ¸öËٶȾͽ«ÊǺܿìµÄ£¬ÒòΪÄúµÄÕâ±¾×ÖµäÕýÎÄÊÇ°´ÈÕÆÚ½øÐÐÅÅÐòµÄ£¬¾ÛÀàË÷ÒýÖ»ÐèÒªÕÒµ½Òª¼ìË÷µÄËùÓÐÊý¾ÝÖеĿªÍ·ºÍ½áβÊý¾Ý¼´¿É£»¶ø²»Ïñ·Ç¾Û¼¯Ë÷Òý£¬±ØÐëÏȲ鵽Ŀ¼Öв鵽ÿһÏîÊý¾Ý¶ÔÓ¦µÄÒ³Â룬ȻºóÔÙ¸ù¾ÝÒ³Âë²éµ½¾ßÌåÄÚÈÝ¡£
£¨Èý£©½áºÏʵ¼Ê£¬Ì¸Ë÷ÒýʹÓõÄÎóÇø
ÀíÂÛµÄÄ¿µÄÊÇÓ¦Óá£ËäÈ»ÎÒÃǸղÅÁгöÁ˺ÎʱӦʹÓþۼ¯Ë÷Òý»ò·Ç¾Û¼¯Ë÷Òý£¬µ«ÔÚʵ¼ùÖÐÒÔÉϹæÔòÈ´ºÜÈÝÒ×±»ºöÊÓ»ò²»Äܸù¾Ýʵ¼ÊÇé¿ö½øÐÐ×ۺϷÖÎö¡£ÏÂÃæÎÒÃǽ«¸ù¾ÝÔÚʵ¼ùÖÐÓöµ½µÄʵ¼ÊÎÊÌâÀ´Ì¸Ò»ÏÂË÷ÒýʹÓõÄÎóÇø£¬ÒÔ±ãÓÚ´ó¼ÒÕÆÎÕË÷Òý½¨Á¢µÄ·½·¨¡£
1¡¢Ö÷¼ü¾ÍÊǾۼ¯Ë÷Òý
ÕâÖÖÏë·¨±ÊÕßÈÏΪÊǼ«¶Ë´íÎóµÄ£¬ÊǶԾۼ¯Ë÷ÒýµÄÒ»ÖÖÀË·Ñ¡£ËäÈ»SQL SERVERĬÈÏÊÇÔÚÖ÷¼üÉϽ¨Á¢¾Û¼¯Ë÷ÒýµÄ¡£
ͨ³££¬ÎÒÃÇ»áÔÚÿ¸ö±íÖж¼½¨Á¢Ò»¸öIDÁУ¬ÒÔÇø·ÖÿÌõÊý¾Ý£¬²¢ÇÒÕâ¸öIDÁÐÊÇ×Ô¶¯Ôö´óµÄ£¬²½³¤Ò»°ãΪ1¡£ÎÒÃǵÄÕâ¸ö°ì¹«×Ô¶¯»¯µÄʵÀýÖеÄÁÐGid¾ÍÊÇÈç´Ë¡£´Ëʱ£¬Èç¹ûÎÒÃǽ«Õâ¸öÁÐÉèΪÖ÷¼ü£¬SQL SERVER»á½«´ËÁÐĬÈÏΪ¾Û¼¯Ë÷Òý¡£ÕâÑù×öÓкô¦£¬¾ÍÊÇ¿ÉÒÔÈÃÄúµÄÊý¾ÝÔÚÊý¾Ý¿âÖа´ÕÕID½øÐÐÎïÀíÅÅÐò£¬µ«±ÊÕßÈÏΪÕâÑù×öÒâÒå²»´ó¡£
ÏÔ¶øÒ×¼û£¬¾Û¼¯Ë÷ÒýµÄÓÅÊÆÊǺÜÃ÷ÏԵģ¬¶øÿ¸ö±íÖÐÖ»ÄÜÓÐÒ»¸ö¾Û¼¯Ë÷ÒýµÄ¹æÔò£¬ÕâʹµÃ¾Û¼¯Ë÷Òý±äµÃ¸ü¼ÓÕä¹ó¡£
´ÓÎÒÃÇÇ°Ãæ̸µ½µÄ¾Û¼¯Ë÷ÒýµÄ¶¨ÒåÎÒÃÇ¿ÉÒÔ¿´³ö£¬Ê¹Óþۼ¯Ë÷ÒýµÄ×î´óºÃ´¦¾ÍÊÇÄܹ»¸ù¾Ý²éѯҪÇó£¬Ñ¸ËÙËõС²éѯ·¶Î§£¬±ÜÃâÈ«±íɨÃè¡£ÔÚʵ¼ÊÓ¦ÓÃÖУ¬ÒòΪIDºÅÊÇ×Ô¶¯Éú³ÉµÄ£¬ÎÒÃDz¢²»ÖªµÀÿÌõ¼Ç¼µÄIDºÅ£¬ËùÒÔÎÒÃǺÜÄÑÔÚʵ¼ùÖÐÓÃIDºÅÀ´½øÐвéѯ¡£Õâ¾ÍʹÈÃIDºÅÕâ¸öÖ÷¼ü×÷Ϊ¾Û¼¯Ë÷Òý³ÉΪһÖÖ×ÊÔ´ÀË·Ñ¡£Æä´Î£¬ÈÃÿ¸öIDºÅ¶¼²»Í¬µÄ×Ö¶Î×÷Ϊ¾Û¼¯Ë÷ÒýÒ²²»·ûºÏ“´óÊýÄ¿µÄ²»Í¬ÖµÇé¿öϲ»Ó¦½¨Á¢¾ÛºÏË÷Òý”¹æÔò£»µ±È»£¬ÕâÖÖÇé¿öÖ»ÊÇÕë¶ÔÓû§¾³£Ð޸ļǼÄÚÈÝ£¬ÌرðÊÇË÷ÒýÏîµÄʱºò»á¸º×÷Ó㬵«¶ÔÓÚ²éѯËٶȲ¢Ã»ÓÐÓ°Ïì¡£
Ôڰ칫×Ô¶¯»¯ÏµÍ³ÖУ¬ÎÞÂÛÊÇϵͳÊ×Ò³ÏÔʾµÄÐèÒªÓû§Ç©ÊÕµÄÎļþ¡¢»áÒ黹ÊÇÓû§½øÐÐÎļþ²éѯµÈÈκÎÇé¿öϽøÐÐÊý¾Ý²éѯ¶¼Àë²»¿ª×ֶεÄÊÇ“ÈÕÆÚ”»¹ÓÐÓû§±¾ÉíµÄ“Óû§Ãû”¡£
ͨ³££¬°ì¹«×Ô¶¯»¯µÄÊ×Ò³»áÏÔʾÿ¸öÓû§ÉÐδǩÊÕµÄÎļþ»ò»áÒé¡£ËäÈ»ÎÒÃǵÄwhereÓï¾ä¿ÉÒÔ½ö½öÏÞÖƵ±Ç°Óû§ÉÐδǩÊÕµÄÇé¿ö£¬µ«Èç¹ûÄúµÄϵͳÒѽ¨Á¢Á˺ܳ¤Ê±¼ä£¬²¢ÇÒÊý¾ÝÁ¿ºÜ´ó£¬ÄÇô£¬Ã¿´Îÿ¸öÓû§´ò¿ªÊ×Ò³µÄʱºò¶¼½øÐÐÒ»´ÎÈ«±íɨÃ裬ÕâÑù×öÒâÒåÊDz»´óµÄ£¬¾ø´ó¶àÊýµÄÓû§1¸öÔÂÇ°µÄÎļþ¶¼ÒѾä¯ÀÀ¹ýÁË£¬ÕâÑù×öÖ»ÄÜͽÔöÊý¾Ý¿âµÄ¿ªÏú¶øÒÑ¡£ÊÂʵÉÏ£¬ÎÒÃÇÍêÈ«¿ÉÒÔÈÃÓû§´ò¿ªÏµÍ³Ê×ҳʱ£¬Êý¾Ý¿â½ö½ö²éѯÕâ¸öÓû§½ü3¸öÔÂÀ´Î´ÔÄÀÀµÄÎļþ£¬Í¨¹ý“ÈÕÆÚ”Õâ¸ö×Ö¶ÎÀ´ÏÞÖƱíɨÃ裬Ìá¸ß²éѯËٶȡ£Èç¹ûÄúµÄ°ì¹«×Ô¶¯»¯ÏµÍ³ÒѾ½¨Á¢µÄ2Ä꣬ÄÇôÄúµÄÊ×Ò³ÏÔʾËÙ¶ÈÀíÂÛÉϽ«ÊÇÔÀ´ËÙ¶È8±¶£¬ÉõÖÁ¸ü¿ì¡£
ÔÚÕâÀïÖ®ËùÒÔÌáµ½“ÀíÂÛÉÏ”Èý×Ö£¬ÊÇÒòΪÈç¹ûÄúµÄ¾Û¼¯Ë÷Òý»¹ÊÇäĿµØ½¨ÔÚIDÕâ¸öÖ÷¼üÉÏʱ£¬ÄúµÄ²éѯËÙ¶ÈÊÇûÓÐÕâô¸ßµÄ£¬¼´Ê¹ÄúÔÚ“ÈÕÆÚ”Õâ¸ö×Ö¶ÎÉϽ¨Á¢µÄË÷Òý£¨·Ç¾ÛºÏË÷Òý£©¡£ÏÂÃæÎÒÃǾÍÀ´¿´Ò»ÏÂÔÚ1000ÍòÌõÊý¾ÝÁ¿µÄÇé¿öϸ÷ÖÖ²éѯµÄËٶȱíÏÖ£¨3¸öÔÂÄÚµÄÊý¾ÝΪ25ÍòÌõ£©£º
£¨1£©½öÔÚÖ÷¼üÉϽ¨Á¢¾Û¼¯Ë÷Òý£¬²¢ÇÒ²»»®·Öʱ¼ä¶Î£º
Select gid,fariqi,neibuyonghu,title from tgongwen
ÓÃʱ£º128470ºÁÃ루¼´£º128Ã룩
£¨2£©ÔÚÖ÷¼üÉϽ¨Á¢¾Û¼¯Ë÷Òý£¬ÔÚfariqÉϽ¨Á¢·Ç¾Û¼¯Ë÷Òý£º
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
ÓÃʱ£º53763ºÁÃ루54Ã룩
£¨3£©½«¾ÛºÏË÷Òý½¨Á¢ÔÚÈÕÆÚÁУ¨fariqi£©ÉÏ£º
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
ÓÃʱ£º2423ºÁÃ루2Ã룩
ËäȻÿÌõÓï¾äÌáÈ¡³öÀ´µÄ¶¼ÊÇ25ÍòÌõÊý¾Ý£¬¸÷ÖÖÇé¿öµÄ²îÒìÈ´ÊǾ޴óµÄ£¬ÌرðÊǽ«¾Û¼¯Ë÷Òý½¨Á¢ÔÚÈÕÆÚÁÐʱµÄ²îÒì¡£ÊÂʵÉÏ£¬Èç¹ûÄúµÄÊý¾Ý¿âÕæµÄÓÐ1000ÍòÈÝÁ¿µÄ»°£¬°ÑÖ÷¼ü½¨Á¢ÔÚIDÁÐÉÏ£¬¾ÍÏñÒÔÉϵĵÚ1¡¢2ÖÖÇé¿ö£¬ÔÚÍøÒ³ÉϵıíÏÖ¾ÍÊdz¬Ê±£¬¸ù±¾¾ÍÎÞ·¨ÏÔʾ¡£ÕâÒ²ÊÇÎÒÞðÆúIDÁÐ×÷Ϊ¾Û¼¯Ë÷ÒýµÄÒ»¸ö×îÖØÒªµÄÒòËØ¡£
µÃ³öÒÔÉÏËٶȵķ½·¨ÊÇ£ºÔÚ¸÷¸öselectÓï¾äÇ°¼Ó£ºdeclare @d datetime
set @d=getdate()
²¢ÔÚselectÓï¾äºó¼Ó£º
select [Óï¾äÖ´Ðл¨·Ñʱ¼ä(ºÁÃë)]=datediff(ms,@d,getdate())
2¡¢Ö»Òª½¨Á¢Ë÷Òý¾ÍÄÜÏÔÖøÌá¸ß²éѯËÙ¶È
ÊÂʵÉÏ£¬ÎÒÃÇ¿ÉÒÔ·¢ÏÖÉÏÃæµÄÀý×ÓÖУ¬µÚ2¡¢3ÌõÓï¾äÍêÈ«Ïàͬ£¬ÇÒ½¨Á¢Ë÷ÒýµÄ×Ö¶ÎÒ²Ïàͬ£»²»Í¬µÄ½öÊÇÇ°ÕßÔÚfariqi×Ö¶ÎÉϽ¨Á¢µÄÊǷǾۺÏË÷Òý£¬ºóÕßÔÚ´Ë×Ö¶ÎÉϽ¨Á¢µÄÊǾۺÏË÷Òý£¬µ«²éѯËÙ¶ÈÈ´ÓÐ×ÅÌìÈÀÖ®±ð¡£ËùÒÔ£¬²¢·ÇÊÇÔÚÈκÎ×Ö¶ÎÉϼòµ¥µØ½¨Á¢Ë÷Òý¾ÍÄÜÌá¸ß²éѯËٶȡ£
´Ó½¨±íµÄÓï¾äÖУ¬ÎÒÃÇ¿ÉÒÔ¿´µ½Õâ¸öÓÐ×Å1000ÍòÊý¾ÝµÄ±íÖÐfariqi×Ö¶ÎÓÐ5003¸ö²»Í¬¼Ç¼¡£ÔÚ´Ë×Ö¶ÎÉϽ¨Á¢¾ÛºÏË÷ÒýÊÇÔÙºÏÊʲ»¹ýÁË¡£ÔÚÏÖʵÖУ¬ÎÒÃÇÿÌ춼»á·¢¼¸¸öÎļþ£¬Õ⼸¸öÎļþµÄ·¢ÎÄÈÕÆÚ¾ÍÏàͬ£¬ÕâÍêÈ«·ûºÏ½¨Á¢¾Û¼¯Ë÷ÒýÒªÇóµÄ£º“¼È²»Äܾø´ó¶àÊý¶¼Ïàͬ£¬ÓÖ²»ÄÜÖ»Óм«ÉÙÊýÏàͬ”µÄ¹æÔò¡£ÓÉ´Ë¿´À´£¬ÎÒÃǽ¨Á¢“Êʵ±”µÄ¾ÛºÏË÷Òý¶ÔÓÚÎÒÃÇÌá¸ß²éѯËÙ¶ÈÊǷdz£ÖØÒªµÄ¡£
3¡¢°ÑËùÓÐÐèÒªÌá¸ß²éѯËٶȵÄ×ֶζ¼¼Ó½ø¾Û¼¯Ë÷Òý£¬ÒÔÌá¸ß²éѯËÙ¶È
ÉÏÃæÒѾ̸µ½£ºÔÚ½øÐÐÊý¾Ý²éѯʱ¶¼Àë²»¿ª×ֶεÄÊÇ“ÈÕÆÚ”»¹ÓÐÓû§±¾ÉíµÄ“Óû§Ãû”¡£¼ÈÈ»ÕâÁ½¸ö×ֶζ¼ÊÇÈç´ËµÄÖØÒª£¬ÎÒÃÇ¿ÉÒÔ°ÑËûÃǺϲ¢ÆðÀ´£¬½¨Á¢Ò»¸ö¸´ºÏË÷Òý£¨compound index£©¡£
ºÜ¶àÈËÈÏΪֻҪ°ÑÈκÎ×ֶμӽø¾Û¼¯Ë÷Òý£¬¾ÍÄÜÌá¸ß²éѯËٶȣ¬Ò²ÓÐÈ˸е½ÃÔ»ó£ºÈç¹û°Ñ¸´ºÏµÄ¾Û¼¯Ë÷Òý×ֶηֿª²éѯ£¬ÄÇô²éѯËٶȻá¼õÂýÂ𣿴ø×ÅÕâ¸öÎÊÌ⣬ÎÒÃÇÀ´¿´Ò»ÏÂÒÔϵIJéѯËٶȣ¨½á¹û¼¯¶¼ÊÇ25ÍòÌõÊý¾Ý£©£º£¨ÈÕÆÚÁÐfariqiÊ×ÏÈÅÅÔÚ¸´ºÏ¾Û¼¯Ë÷ÒýµÄÆðʼÁУ¬Óû§ÃûneibuyonghuÅÅÔÚºóÁУ©
£¨1£©select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
²éѯËٶȣº2513ºÁÃë
£¨2£©select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='°ì¹«ÊÒ'
²éѯËٶȣº2516ºÁÃë
£¨3£©select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='°ì¹«ÊÒ'
²éѯËٶȣº60280ºÁÃë
´ÓÒÔÉÏÊÔÑéÖУ¬ÎÒÃÇ¿ÉÒÔ¿´µ½Èç¹û½öÓþۼ¯Ë÷ÒýµÄÆðʼÁÐ×÷Ϊ²éѯÌõ¼þºÍͬʱÓõ½¸´ºÏ¾Û¼¯Ë÷ÒýµÄÈ«²¿ÁеIJéѯËÙ¶ÈÊǼ¸ºõÒ»ÑùµÄ£¬ÉõÖÁ±ÈÓÃÉÏÈ«²¿µÄ¸´ºÏË÷ÒýÁл¹ÒªÂԿ죨ÔÚ²éѯ½á¹û¼¯ÊýÄ¿Ò»ÑùµÄÇé¿öÏ£©£»¶øÈç¹û½öÓø´ºÏ¾Û¼¯Ë÷ÒýµÄ·ÇÆðʼÁÐ×÷Ϊ²éѯÌõ¼þµÄ»°£¬Õâ¸öË÷ÒýÊDz»ÆðÈκÎ×÷Óõġ£µ±È»£¬Óï¾ä1¡¢2µÄ²éѯËÙ¶ÈÒ»ÑùÊÇÒòΪ²éѯµÄÌõÄ¿ÊýÒ»Ñù£¬Èç¹û¸´ºÏË÷ÒýµÄËùÓÐÁж¼ÓÃÉÏ£¬¶øÇÒ²éѯ½á¹ûÉٵĻ°£¬ÕâÑù¾Í»áÐγɓË÷Òý¸²¸Ç”£¬Òò¶øÐÔÄÜ¿ÉÒÔ´ïµ½×îÓÅ¡£Í¬Ê±£¬Çë¼Çס£ºÎÞÂÛÄúÊÇ·ñ¾³£Ê¹ÓþۺÏË÷ÒýµÄÆäËûÁУ¬µ«ÆäÇ°µ¼ÁÐÒ»¶¨ÒªÊÇʹÓÃ×îƵ·±µÄÁС£
£¨ËÄ£©ÆäËûÊéÉÏûÓеÄË÷ÒýʹÓþÑé×ܽá
1¡¢ÓþۺÏË÷Òý±ÈÓò»ÊǾۺÏË÷ÒýµÄÖ÷¼üËٶȿì
ÏÂÃæÊÇʵÀýÓï¾ä£º£¨¶¼ÊÇÌáÈ¡25ÍòÌõÊý¾Ý£©
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
ʹÓÃʱ¼ä£º3326ºÁÃë
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
ʹÓÃʱ¼ä£º4470ºÁÃë
ÕâÀÓþۺÏË÷Òý±ÈÓò»ÊǾۺÏË÷ÒýµÄÖ÷¼üËٶȿìÁ˽ü1/4¡£
2¡¢ÓþۺÏË÷Òý±ÈÓÃÒ»°ãµÄÖ÷¼ü×÷order byʱËٶȿ죬ÌرðÊÇÔÚСÊý¾ÝÁ¿Çé¿öÏÂ
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
ÓÃʱ£º12936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
ÓÃʱ£º18843
ÕâÀÓþۺÏË÷Òý±ÈÓÃÒ»°ãµÄÖ÷¼ü×÷order byʱ£¬ËٶȿìÁË3/10¡£ÊÂʵÉÏ£¬Èç¹ûÊý¾ÝÁ¿ºÜСµÄ»°£¬Óþۼ¯Ë÷Òý×÷ΪÅÅÐòÁÐÒª±ÈʹÓ÷Ǿۼ¯Ë÷ÒýËٶȿìµÃÃ÷ÏԵĶࣻ¶øÊý¾ÝÁ¿Èç¹ûºÜ´óµÄ»°£¬Èç10ÍòÒÔÉÏ£¬Ôò¶þÕßµÄËٶȲî±ð²»Ã÷ÏÔ¡£
3¡¢Ê¹ÓþۺÏË÷ÒýÄÚµÄʱ¼ä¶Î£¬ËÑË÷ʱ¼ä»á°´Êý¾ÝÕ¼Õû¸öÊý¾Ý±íµÄ°Ù·Ö±È³É±ÈÀý¼õÉÙ£¬¶øÎÞÂÛ¾ÛºÏË÷ÒýʹÓÃÁ˶àÉÙ¸ö
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
ÓÃʱ£º6343ºÁÃ루ÌáÈ¡100ÍòÌõ£©
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
ÓÃʱ£º3170ºÁÃ루ÌáÈ¡50ÍòÌõ£©
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
ÓÃʱ£º3326ºÁÃ루ºÍÉϾäµÄ½á¹ûһģһÑù¡£Èç¹û²É¼¯µÄÊýÁ¿Ò»Ñù£¬ÄÇôÓôóÓںź͵ÈÓÚºÅÊÇÒ»ÑùµÄ£©
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
ÓÃʱ£º3280ºÁÃë
4 ¡¢ÈÕÆÚÁв»»áÒòΪÓзÖÃëµÄÊäÈë¶ø¼õÂý²éѯËÙ¶È
ÏÂÃæµÄÀý×ÓÖУ¬¹²ÓÐ100ÍòÌõÊý¾Ý£¬2004Äê1ÔÂ1ÈÕÒÔºóµÄÊý¾ÝÓÐ50ÍòÌõ£¬µ«Ö»ÓÐÁ½¸ö²»Í¬µÄÈÕÆÚ£¬ÈÕÆÚ¾«È·µ½ÈÕ£»Ö®Ç°ÓÐÊý¾Ý50ÍòÌõ£¬ÓÐ5000¸ö²»Í¬µÄÈÕÆÚ£¬ÈÕÆÚ¾«È·µ½Ãë¡£
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi
ÓÃʱ£º6390ºÁÃë
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi
ÓÃʱ£º6453ºÁÃë
£¨Î壩ÆäËû×¢ÒâÊÂÏî
“Ë®¿ÉÔØÖÛ£¬Òà¿É¸²ÖÛ”£¬Ë÷ÒýÒ²Ò»Ñù¡£Ë÷ÒýÓÐÖúÓÚÌá¸ß¼ìË÷ÐÔÄÜ£¬µ«¹ý¶à»ò²»µ±µÄË÷ÒýÒ²»áµ¼ÖÂϵͳµÍЧ¡£ÒòΪÓû§ÔÚ±íÖÐÿ¼Ó½øÒ»¸öË÷Òý£¬Êý¾Ý¿â¾ÍÒª×ö¸ü¶àµÄ¹¤×÷¡£¹ý¶àµÄË÷ÒýÉõÖÁ»áµ¼ÖÂË÷ÒýËéƬ¡£
ËùÒÔ˵£¬ÎÒÃÇÒª½¨Á¢Ò»¸ö“Êʵ±”µÄË÷ÒýÌåϵ£¬ÌرðÊǶԾۺÏË÷ÒýµÄ´´½¨£¬¸üÓ¦¾«ÒæÇ󾫣¬ÒÔʹÄúµÄÊý¾Ý¿âÄܵõ½¸ßÐÔÄܵķ¢»Ó¡£
µ±È»£¬ÔÚʵ¼ùÖУ¬×÷Ϊһ¸ö¾¡Ö°µÄÊý¾Ý¿â¹ÜÀíÔ±£¬Äú»¹Òª¶à²âÊÔһЩ·½°¸£¬ÕÒ³öÄÄÖÖ·½°¸Ð§ÂÊ×î¸ß¡¢×îΪÓÐЧ¡£
¶þ¡¢¸ÄÉÆSQLÓï¾ä
ºÜ¶àÈ˲»ÖªµÀSQLÓï¾äÔÚSQL SERVERÖÐÊÇÈçºÎÖ´Ðеģ¬ËûÃǵ£ÐÄ×Ô¼ºËùдµÄSQLÓï¾ä»á±»SQL SERVERÎó½â¡£±ÈÈ磺
select * from table1 where name='zhangsan' and tID > 10000
ºÍÖ´ÐÐ:
select * from table1 where tID > 10000 and name='zhangsan'
һЩÈ˲»ÖªµÀÒÔÉÏÁ½ÌõÓï¾äµÄÖ´ÐÐЧÂÊÊÇ·ñÒ»Ñù£¬ÒòΪÈç¹û¼òµ¥µÄ´ÓÓï¾äÏȺóÉÏ¿´£¬ÕâÁ½¸öÓï¾äµÄÈ·ÊDz»Ò»Ñù£¬Èç¹ûtIDÊÇÒ»¸ö¾ÛºÏË÷Òý£¬ÄÇôºóÒ»¾ä½ö½ö´Ó±íµÄ10000ÌõÒÔºóµÄ¼Ç¼ÖвéÕÒ¾ÍÐÐÁË£»¶øÇ°Ò»¾äÔòÒªÏÈ´ÓÈ«±íÖвéÕÒ¿´Óм¸¸öname='zhangsan'µÄ£¬¶øºóÔÙ¸ù¾ÝÏÞÖÆÌõ¼þÌõ¼þtID>10000À´Ìá³ö²éѯ½á¹û¡£
ÊÂʵÉÏ£¬ÕâÑùµÄµ£ÐÄÊDz»±ØÒªµÄ¡£SQL SERVERÖÐÓÐÒ»¸ö“²éѯ·ÖÎöÓÅ»¯Æ÷”£¬Ëü¿ÉÒÔ¼ÆËã³öwhere×Ó¾äÖеÄËÑË÷Ìõ¼þ²¢È·¶¨ÄĸöË÷ÒýÄÜËõС±íɨÃèµÄËÑË÷¿Õ¼ä£¬Ò²¾ÍÊÇ˵£¬ËüÄÜʵÏÖ×Ô¶¯ÓÅ»¯¡£
ËäÈ»²éѯÓÅ»¯Æ÷¿ÉÒÔ¸ù¾Ýwhere×Ó¾ä×Ô¶¯µÄ½øÐвéѯÓÅ»¯£¬µ«´ó¼ÒÈÔÈ»ÓбØÒªÁ˽âһϓ²éѯÓÅ»¯Æ÷”µÄ¹¤×÷ÔÀí£¬Èç·ÇÕâÑù£¬ÓÐʱ²éѯÓÅ»¯Æ÷¾Í»á²»°´ÕÕÄúµÄ±¾Òâ½øÐпìËÙ²éѯ¡£
ÔÚ²éѯ·ÖÎö½×¶Î£¬²éѯÓÅ»¯Æ÷²é¿´²éѯµÄÿ¸ö½×¶Î²¢¾ö¶¨ÏÞÖÆÐèҪɨÃèµÄÊý¾ÝÁ¿ÊÇ·ñÓÐÓá£Èç¹ûÒ»¸ö½×¶Î¿ÉÒÔ±»ÓÃ×÷Ò»¸öɨÃè²ÎÊý£¨SARG£©£¬ÄÇô¾Í³Æ֮Ϊ¿ÉÓÅ»¯µÄ£¬²¢ÇÒ¿ÉÒÔÀûÓÃË÷Òý¿ìËÙ»ñµÃËùÐèÊý¾Ý¡£
SARGµÄ¶¨Ò壺ÓÃÓÚÏÞÖÆËÑË÷µÄÒ»¸ö²Ù×÷£¬ÒòΪËüͨ³£ÊÇÖ¸Ò»¸öÌض¨µÄÆ¥Å䣬һ¸öÖµµÃ·¶Î§ÄÚµÄÆ¥Åä»òÕßÁ½¸öÒÔÉÏÌõ¼þµÄANDÁ¬½Ó¡£ÐÎʽÈçÏ£º
ÁÐÃû ²Ù×÷·û <³£Êý »ò ±äÁ¿>
»ò
<³£Êý »ò ±äÁ¿> ²Ù×÷·ûÁÐÃû
ÁÐÃû¿ÉÒÔ³öÏÖÔÚ²Ù×÷·ûµÄÒ»±ß£¬¶ø³£Êý»ò±äÁ¿³öÏÖÔÚ²Ù×÷·ûµÄÁíÒ»±ß¡£È磺
Name=’ÕÅÈý’
¼Û¸ñ>5000
5000<¼Û¸ñ
Name=’ÕÅÈý’ and ¼Û¸ñ>5000
Èç¹ûÒ»¸ö±í´ïʽ²»ÄÜÂú×ãSARGµÄÐÎʽ£¬ÄÇËü¾ÍÎÞ·¨ÏÞÖÆËÑË÷µÄ·¶Î§ÁË£¬Ò²¾ÍÊÇSQL SERVER±ØÐë¶ÔÿһÐж¼ÅжÏËüÊÇ·ñÂú×ãWHERE×Ó¾äÖеÄËùÓÐÌõ¼þ¡£ËùÒÔÒ»¸öË÷Òý¶ÔÓÚ²»Âú×ãSARGÐÎʽµÄ±í´ïʽÀ´ËµÊÇÎÞÓõġ£
½éÉÜÍêSARGºó£¬ÎÒÃÇÀ´×ܽáÒ»ÏÂʹÓÃSARGÒÔ¼°ÔÚʵ¼ùÖÐÓöµ½µÄºÍijЩ×ÊÁÏÉϽáÂÛ²»Í¬µÄ¾Ñ飺
1¡¢LikeÓï¾äÊÇ·ñÊôÓÚSARGÈ¡¾öÓÚËùʹÓõÄͨÅä·ûµÄÀàÐÍ
È磺name like ‘ÕÅ%’ £¬Õâ¾ÍÊôÓÚSARG
¶ø£ºname like ‘%ÕÅ’ ,¾Í²»ÊôÓÚSARG¡£
ÔÒòÊÇͨÅä·û%ÔÚ×Ö·û´®µÄ¿ªÍ¨Ê¹µÃË÷ÒýÎÞ·¨Ê¹Óá£
2¡¢or »áÒýÆðÈ«±íɨÃè
Name=’ÕÅÈý’ and ¼Û¸ñ>5000 ·ûºÅSARG£¬¶ø£ºName=’ÕÅÈý’ or ¼Û¸ñ>5000 Ôò²»·ûºÏSARG¡£Ê¹ÓÃor»áÒýÆðÈ«±íɨÃè¡£
3¡¢·Ç²Ù×÷·û¡¢º¯ÊýÒýÆðµÄ²»Âú×ãSARGÐÎʽµÄÓï¾ä
²»Âú×ãSARGÐÎʽµÄÓï¾ä×îµäÐ͵ÄÇé¿ö¾ÍÊÇ°üÀ¨·Ç²Ù×÷·ûµÄÓï¾ä£¬È磺NOT¡¢!=¡¢<>¡¢!<¡¢!>¡¢NOT EXISTS¡¢NOT IN¡¢NOT LIKEµÈ£¬ÁíÍ⻹Óк¯Êý¡£ÏÂÃæ¾ÍÊǼ¸¸ö²»Âú×ãSARGÐÎʽµÄÀý×Ó£º
ABS(¼Û¸ñ)<5000
Name like ‘%Èý’
ÓÐЩ±í´ïʽ£¬È磺
WHERE ¼Û¸ñ*2>5000
SQL SERVERÒ²»áÈÏΪÊÇSARG£¬SQL SERVER»á½«´Ëʽת»¯Îª£º
WHERE ¼Û¸ñ>2500/2
µ«ÎÒÃDz»ÍƼöÕâÑùʹÓã¬ÒòΪÓÐʱSQL SERVER²»Äܱ£Ö¤ÕâÖÖת»¯ÓëÔʼ±í´ïʽÊÇÍêÈ«µÈ¼ÛµÄ¡£
4¡¢IN µÄ×÷ÓÃÏ൱ÓëOR
Óï¾ä£º
Select * from table1 where tid in (2,3)
ºÍ
Select * from table1 where tid=2 or tid=3
ÊÇÒ»ÑùµÄ£¬¶¼»áÒýÆðÈ«±íɨÃ裬Èç¹ûtidÉÏÓÐË÷Òý£¬ÆäË÷ÒýÒ²»áʧЧ¡£
5¡¢¾¡Á¿ÉÙÓÃNOT
6¡¢exists ºÍ in µÄÖ´ÐÐЧÂÊÊÇÒ»ÑùµÄ
ºÜ¶à×ÊÁÏÉ϶¼ÏÔʾ˵£¬existsÒª±ÈinµÄÖ´ÐÐЧÂÊÒª¸ß£¬Í¬Ê±Ó¦¾¡¿ÉÄܵÄÓÃnot existsÀ´´úÌænot in¡£µ«ÊÂʵÉÏ£¬ÎÒÊÔÑéÁËһϣ¬·¢ÏÖ¶þÕßÎÞÂÛÊÇÇ°Ãæ´ø²»´ønot£¬¶þÕßÖ®¼äµÄÖ´ÐÐЧÂʶ¼ÊÇÒ»ÑùµÄ¡£ÒòΪÉæ¼°×Ó²éѯ£¬ÎÒÃÇÊÔÑéÕâ´ÎÓÃSQL SERVER×Ô´øµÄpubsÊý¾Ý¿â¡£ÔËÐÐÇ°ÎÒÃÇ¿ÉÒÔ°ÑSQL SERVERµÄstatistics I/O״̬´ò¿ª¡£
£¨1£©select title,price from titles where title_id in (select title_id from sales where qty>30)
¸Ã¾äµÄÖ´Ðнá¹ûΪ£º
±í 'sales'¡£É¨Ãè¼ÆÊý 18£¬Âß¼¶Á 56 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
±í 'titles'¡£É¨Ãè¼ÆÊý 1£¬Âß¼¶Á 2 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
£¨2£©select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
µÚ¶þ¾äµÄÖ´Ðнá¹ûΪ£º
±í 'sales'¡£É¨Ãè¼ÆÊý 18£¬Âß¼¶Á 56 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
±í 'titles'¡£É¨Ãè¼ÆÊý 1£¬Âß¼¶Á 2 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
ÎÒÃÇ´Ó´Ë¿ÉÒÔ¿´µ½ÓÃexistsºÍÓÃinµÄÖ´ÐÐЧÂÊÊÇÒ»ÑùµÄ¡£
7¡¢Óú¯Êýcharindex()ºÍÇ°Ãæ¼ÓͨÅä·û%µÄLIKEÖ´ÐÐЧÂÊÒ»Ñù
Ç°Ã棬ÎÒÃÇ̸µ½£¬Èç¹ûÔÚLIKEÇ°Ãæ¼ÓÉÏͨÅä·û%£¬ÄÇô½«»áÒýÆðÈ«±íɨÃ裬ËùÒÔÆäÖ´ÐÐЧÂÊÊǵÍϵġ£µ«ÓеÄ×ÊÁϽéÉÜ˵£¬Óú¯Êýcharindex()À´´úÌæLIKEËٶȻáÓдóµÄÌáÉý£¬¾ÎÒÊÔÑ飬·¢ÏÖÕâÖÖ˵Ã÷Ò²ÊÇ´íÎóµÄ£º
select gid,title,fariqi,reader from tgongwen where charindex('ÐÌÕìÖ§¶Ó',reader)>0 and fariqi>'2004-5-5'
ÓÃʱ£º7Ã룬ÁíÍ⣺ɨÃè¼ÆÊý 4£¬Âß¼¶Á 7155 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
select gid,title,fariqi,reader from tgongwen where reader like '%' + 'ÐÌÕìÖ§¶Ó' + '%' and fariqi>'2004-5-5'
ÓÃʱ£º7Ã룬ÁíÍ⣺ɨÃè¼ÆÊý 4£¬Âß¼¶Á 7155 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
8¡¢union²¢²»¾ø¶Ô±ÈorµÄÖ´ÐÐЧÂʸß
ÎÒÃÇÇ°ÃæÒѾ̸µ½ÁËÔÚwhere×Ó¾äÖÐʹÓÃor»áÒýÆðÈ«±íɨÃ裬һ°ãµÄ£¬ÎÒËù¼û¹ýµÄ×ÊÁ϶¼ÊÇÍƼöÕâÀïÓÃunionÀ´´úÌæor¡£ÊÂʵ֤Ã÷£¬ÕâÖÖ˵·¨¶ÔÓڴ󲿷ֶ¼ÊÇÊÊÓõġ£
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000
ÓÃʱ£º68Ã롣ɨÃè¼ÆÊý 1£¬Âß¼¶Á 404008 ´Î£¬ÎïÀí¶Á 283 ´Î£¬Ô¤¶Á 392163 ´Î¡£
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
ÓÃʱ£º9Ã롣ɨÃè¼ÆÊý 8£¬Âß¼¶Á 67489 ´Î£¬ÎïÀí¶Á 216 ´Î£¬Ô¤¶Á 7499 ´Î¡£
¿´À´£¬ÓÃunionÔÚͨ³£Çé¿öϱÈÓÃorµÄЧÂÊÒª¸ßµÄ¶à¡£
µ«¾¹ýÊÔÑ飬±ÊÕß·¢ÏÖÈç¹ûorÁ½±ßµÄ²éѯÁÐÊÇÒ»ÑùµÄ»°£¬ÄÇôÓÃunionÔò·´µ¹ºÍÓÃorµÄÖ´ÐÐËٶȲîºÜ¶à£¬ËäÈ»ÕâÀïunionɨÃèµÄÊÇË÷Òý£¬¶øorɨÃèµÄÊÇÈ«±í¡£
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'
ÓÃʱ£º6423ºÁÃ롣ɨÃè¼ÆÊý 2£¬Âß¼¶Á 14726 ´Î£¬ÎïÀí¶Á 1 ´Î£¬Ô¤¶Á 7176 ´Î¡£
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'
ÓÃʱ£º11640ºÁÃ롣ɨÃè¼ÆÊý 8£¬Âß¼¶Á 14806 ´Î£¬ÎïÀí¶Á 108 ´Î£¬Ô¤¶Á 1144 ´Î¡£
9¡¢×Ö¶ÎÌáÈ¡Òª°´ÕÕ“Ðè¶àÉÙ¡¢Ìá¶àÉÙ”µÄÔÔò£¬±ÜÃâ“select *”
ÎÒÃÇÀ´×öÒ»¸öÊÔÑ飺
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
ÓÃʱ£º4673ºÁÃë
select top 10000 gid,fariqi,title from tgongwen order by gid desc
ÓÃʱ£º1376ºÁÃë
select top 10000 gid,fariqi from tgongwen order by gid desc
ÓÃʱ£º80ºÁÃë
ÓÉ´Ë¿´À´£¬ÎÒÃÇÿÉÙÌáÈ¡Ò»¸ö×ֶΣ¬Êý¾ÝµÄÌáÈ¡ËٶȾͻáÓÐÏàÓ¦µÄÌáÉý¡£ÌáÉýµÄËٶȻ¹Òª¿´ÄúÉáÆúµÄ×ֶεĴóСÀ´Åжϡ£
10¡¢count(*)²»±Ècount(×Ö¶Î)Âý
ijЩ×ÊÁÏÉÏ˵£ºÓÃ*»áͳ¼ÆËùÓÐÁУ¬ÏÔȻҪ±ÈÒ»¸öÊÀ½çµÄÁÐÃûЧÂʵ͡£ÕâÖÖ˵·¨ÆäʵÊÇûÓиù¾ÝµÄ¡£ÎÒÃÇÀ´¿´£º
select count(*) from Tgongwen
ÓÃʱ£º1500ºÁÃë
select count(gid) from Tgongwen
ÓÃʱ£º1483ºÁÃë
select count(fariqi) from Tgongwen
ÓÃʱ£º3140ºÁÃë
select count(title) from Tgongwen
ÓÃʱ£º52050ºÁÃë
´ÓÒÔÉÏ¿ÉÒÔ¿´³ö£¬Èç¹ûÓÃcount(*)ºÍÓÃcount(Ö÷¼ü)µÄËÙ¶ÈÊÇÏ൱µÄ£¬¶øcount(*)È´±ÈÆäËûÈκγýÖ÷¼üÒÔÍâµÄ×ֶλã×ÜËÙ¶ÈÒª¿ì£¬¶øÇÒ×Ö¶ÎÔ½³¤£¬»ã×ܵÄËٶȾÍÔ½Âý¡£ÎÒÏ룬Èç¹ûÓÃcount(*)£¬ SQL SERVER¿ÉÄÜ»á×Ô¶¯²éÕÒ×îС×Ö¶ÎÀ´»ã×ܵġ£µ±È»£¬Èç¹ûÄúÖ±½Óдcount(Ö÷¼ü)½«»áÀ´µÄ¸üÖ±½ÓЩ¡£
11¡¢order by°´¾Û¼¯Ë÷ÒýÁÐÅÅÐòЧÂÊ×î¸ß
ÎÒÃÇÀ´¿´£º£¨gidÊÇÖ÷¼ü£¬fariqiÊǾۺÏË÷ÒýÁУ©
select top 10000 gid,fariqi,reader,title from tgongwen
ÓÃʱ£º196 ºÁÃë¡£ ɨÃè¼ÆÊý 1£¬Âß¼¶Á 289 ´Î£¬ÎïÀí¶Á 1 ´Î£¬Ô¤¶Á 1527 ´Î¡£
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
ÓÃʱ£º4720ºÁÃë¡£ ɨÃè¼ÆÊý 1£¬Âß¼¶Á 41956 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 1287 ´Î¡£
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
ÓÃʱ£º4736ºÁÃë¡£ ɨÃè¼ÆÊý 1£¬Âß¼¶Á 55350 ´Î£¬ÎïÀí¶Á 10 ´Î£¬Ô¤¶Á 775 ´Î¡£
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
ÓÃʱ£º173ºÁÃë¡£ ɨÃè¼ÆÊý 1£¬Âß¼¶Á 290 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
ÓÃʱ£º156ºÁÃë¡£ ɨÃè¼ÆÊý 1£¬Âß¼¶Á 289 ´Î£¬ÎïÀí¶Á 0 ´Î£¬Ô¤¶Á 0 ´Î¡£
´ÓÒÔÉÏÎÒÃÇ¿ÉÒÔ¿´³ö£¬²»ÅÅÐòµÄËÙ¶ÈÒÔ¼°Âß¼¶Á´ÎÊý¶¼ÊǺ͓order by ¾Û¼¯Ë÷ÒýÁД µÄËÙ¶ÈÊÇÏ൱µÄ£¬µ«ÕâЩ¶¼±È“order by ·Ç¾Û¼¯Ë÷ÒýÁДµÄ²éѯËÙ¶ÈÊÇ¿ìµÃ¶àµÄ¡£
ͬʱ£¬°´ÕÕij¸ö×ֶνøÐÐÅÅÐòµÄʱºò£¬ÎÞÂÛÊÇÕýÐò»¹Êǵ¹Ðò£¬ËÙ¶ÈÊÇ»ù±¾Ï൱µÄ¡£
12¡¢¸ßЧµÄTOP
ÊÂʵÉÏ£¬ÔÚ²éѯºÍÌáÈ¡³¬´óÈÝÁ¿µÄÊý¾Ý¼¯Ê±£¬Ó°ÏìÊý¾Ý¿âÏìӦʱ¼äµÄ×î´óÒòËز»ÊÇÊý¾Ý²éÕÒ£¬¶øÊÇÎïÀíµÄI/0²Ù×÷¡£È磺
select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu='°ì¹«ÊÒ'
order by gid desc) as a
order by gid asc
ÕâÌõÓï¾ä£¬´ÓÀíÂÛÉϽ²£¬ÕûÌõÓï¾äµÄÖ´ÐÐʱ¼äÓ¦¸Ã±È×Ó¾äµÄÖ´ÐÐʱ¼ä³¤£¬µ«ÊÂʵÏà·´¡£ÒòΪ£¬×Ó¾äÖ´Ðк󷵻صÄÊÇ10000Ìõ¼Ç¼£¬¶øÕûÌõÓï¾ä½ö·µ»Ø10ÌõÓï¾ä£¬ËùÒÔÓ°ÏìÊý¾Ý¿âÏìӦʱ¼ä×î´óµÄÒòËØÊÇÎïÀíI/O²Ù×÷¡£¶øÏÞÖÆÎïÀíI/O²Ù×÷´Ë´¦µÄ×îÓÐЧ·½·¨Ö®Ò»¾ÍÊÇʹÓÃTOP¹Ø¼ü´ÊÁË¡£TOP¹Ø¼ü´ÊÊÇSQL SERVERÖо¹ýϵͳÓÅ»¯¹ýµÄÒ»¸öÓÃÀ´ÌáÈ¡Ç°¼¸Ìõ»òÇ°¼¸¸ö°Ù·Ö±ÈÊý¾ÝµÄ´Ê¡£¾±ÊÕßÔÚʵ¼ùÖеÄÓ¦Ó㬷¢ÏÖTOPȷʵºÜºÃÓã¬Ð§ÂÊÒ²ºÜ¸ß¡£µ«Õâ¸ö´ÊÔÚÁíÍâÒ»¸ö´óÐÍÊý¾Ý¿âORACLEÖÐȴûÓУ¬Õâ²»ÄÜ˵²»ÊÇÒ»¸öÒź¶£¬ËäÈ»ÔÚORACLEÖпÉÒÔÓÃÆäËû·½·¨£¨È磺rownumber£©À´½â¾ö¡£ÔÚÒÔºóµÄ¹ØÓړʵÏÖǧÍò¼¶Êý¾ÝµÄ·ÖÒ³ÏÔʾ´æ´¢¹ý³Ì”µÄÌÖÂÛÖУ¬ÎÒÃǾͽ«Óõ½TOPÕâ¸ö¹Ø¼ü´Ê¡£
µ½´ËΪֹ£¬ÎÒÃÇÉÏÃæÌÖÂÛÁËÈçºÎʵÏÖ´Ó´óÈÝÁ¿µÄÊý¾Ý¿âÖпìËٵزéѯ³öÄúËùÐèÒªµÄÊý¾Ý·½·¨¡£µ±È»£¬ÎÒÃǽéÉܵÄÕâЩ·½·¨¶¼ÊÇ“Èí”·½·¨£¬ÔÚʵ¼ùÖУ¬ÎÒÃÇ»¹Òª¿¼ÂǸ÷ÖÖ“Ó²”ÒòËØ£¬È磺ÍøÂçÐÔÄÜ¡¢·þÎñÆ÷µÄÐÔÄÜ¡¢²Ù×÷ϵͳµÄÐÔÄÜ£¬ÉõÖÁÍø¿¨¡¢½»»»»úµÈ¡£
Èý¡¢ÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
½¨Á¢Ò»¸ö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µÄ×îм¼Êõ¡ª¡ª±í±äÁ¿¡£Ó¦¸Ã˵Õâ¸ö´æ´¢¹ý³ÌÒ²ÊÇÒ»¸ö·Ç³£ÓÅÐãµÄ·ÖÒ³´æ´¢¹ý³Ì¡£µ±È»£¬ÔÚÕâ¸ö¹ý³ÌÖУ¬ÄúÒ²¿ÉÒÔ°ÑÆäÖеıí±äÁ¿Ð´³ÉÁÙʱ±í£ºCREATE TABLE #Temp¡£µ«ºÜÃ÷ÏÔ£¬ÔÚSQL SERVERÖУ¬ÓÃÁÙʱ±íÊÇûÓÐÓñí±äÁ¿¿ìµÄ¡£ËùÒÔ±ÊÕ߸տªÊ¼Ê¹ÓÃÕâ¸ö´æ´¢¹ý³Ìʱ£¬¸Ð¾õ·Ç³£µÄ²»´í£¬ËÙ¶ÈÒ²±ÈÔÀ´µÄADOµÄºÃ¡£µ«ºóÀ´£¬ÎÒÓÖ·¢ÏÖÁ˱ȴ˷½·¨¸üºÃµÄ·½·¨¡£
±ÊÕßÔøÔÚÍøÉÏ¿´µ½ÁËһƪС¶ÌÎÄ¡¶´ÓÊý¾Ý±íÖÐÈ¡³öµÚnÌõµ½µÚmÌõµÄ¼Ç¼µÄ·½·¨¡·£¬È«ÎÄÈçÏ£º
´Ópublish ±íÖÐÈ¡³öµÚ n Ìõµ½µÚ m ÌõµÄ¼Ç¼£º
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
¡¡¡¡¡¡¡¡(SELECT TOP n-1 id
¡¡¡¡¡¡¡¡ FROM publish))
id Ϊpublish ±íµÄ¹Ø¼ü×Ö
ÎÒµ±Ê±¿´µ½ÕâƪÎÄÕµÄʱºò£¬ÕæµÄÊǾ«ÉñΪ֮һÕñ£¬¾õµÃ˼··Ç³£µÃºÃ¡£µÈµ½ºóÀ´£¬ÎÒÔÚ×÷°ì¹«×Ô¶¯»¯ÏµÍ³£¨ASP.NET+ C#£«SQL SERVER£©µÄʱºò£¬ºöÈ»ÏëÆðÁËÕâƪÎÄÕ£¬ÎÒÏëÈç¹û°ÑÕâ¸öÓï¾ä¸ÄÔìһϣ¬Õâ¾Í¿ÉÄÜÊÇÒ»¸ö·Ç³£ºÃµÄ·ÖÒ³´æ´¢¹ý³Ì¡£ÓÚÊÇÎÒ¾ÍÂúÍøÉÏÕÒÕâƪÎÄÕ£¬Ã»Ïëµ½£¬ÎÄÕ»¹Ã»ÕÒµ½£¬È´ÕÒµ½ÁËһƪ¸ù¾Ý´ËÓï¾äдµÄÒ»¸ö·ÖÒ³´æ´¢¹ý³Ì£¬Õâ¸ö´æ´¢¹ý³ÌÒ²ÊÇÄ¿Ç°½ÏΪÁ÷ÐеÄÒ»ÖÖ·ÖÒ³´æ´¢¹ý³Ì£¬ÎҺܺó»ÚûÓÐÕùÏÈ°ÑÕâ¶ÎÎÄ×Ö¸ÄÔì³É´æ´¢¹ý³Ì£º
CREATE PROCEDURE pagination2
(
@SQL nVARCHAR(4000), --²»´øÅÅÐòÓï¾äµÄSQLÓï¾ä
@Page int, --Ò³Âë
@RecsPerPage int, --ÿҳÈÝÄɵļǼÊý
@ID VARCHAR(255), --ÐèÒªÅÅÐòµÄ²»Öظ´µÄIDºÅ
@Sort VARCHAR(255) --ÅÅÐò×ֶμ°¹æÔò
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
Æäʵ£¬ÒÔÉÏÓï¾ä¿ÉÒÔ¼ò»¯Îª£º
SELECT TOP Ò³´óС *
FROM Table1
WHERE (ID NOT IN
(SELECT TOP Ò³´óС*Ò³Êý id
FROM ±í
ORDER BY id))
ORDER BY ID
µ«Õâ¸ö´æ´¢¹ý³ÌÓÐÒ»¸öÖÂÃüµÄȱµã£¬¾ÍÊÇËüº¬ÓÐNOT IN×ÖÑù¡£ËäÈ»ÎÒ¿ÉÒÔ°ÑËü¸ÄÔìΪ£º
SELECT TOP Ò³´óС *
FROM Table1
WHERE not exists
(select * from (select top (Ò³´óС*Ò³Êý) * from table1 order by id) b where b.id=a.id )
order by id
¼´£¬ÓÃnot existsÀ´´úÌænot in£¬µ«ÎÒÃÇÇ°ÃæÒѾ̸¹ýÁË£¬¶þÕßµÄÖ´ÐÐЧÂÊʵ¼ÊÉÏÊÇûÓÐÇø±ðµÄ¡£
¼È±ãÈç´Ë£¬ÓÃTOP ½áºÏNOT INµÄÕâ¸ö·½·¨»¹ÊDZÈÓÃÓαêÒªÀ´µÃ¿ìһЩ¡£
ËäÈ»ÓÃnot exists²¢²»ÄÜÍì¾ÈÉϸö´æ´¢¹ý³ÌµÄЧÂÊ£¬µ«Ê¹ÓÃSQL SERVERÖеÄTOP¹Ø¼ü×ÖÈ´ÊÇÒ»¸ö·Ç³£Ã÷ÖǵÄÑ¡Ôñ¡£ÒòΪ·ÖÒ³ÓÅ»¯µÄ×îÖÕÄ¿µÄ¾ÍÊDZÜÃâ²úÉú¹ý´óµÄ¼Ç¼¼¯£¬¶øÎÒÃÇÔÚÇ°ÃæÒ²ÒѾÌáµ½ÁËTOPµÄÓÅÊÆ£¬Í¨¹ýTOP ¼´¿ÉʵÏÖ¶ÔÊý¾ÝÁ¿µÄ¿ØÖÆ¡£
ÔÚ·ÖÒ³Ëã·¨ÖУ¬Ó°ÏìÎÒÃDzéѯËٶȵĹؼüÒòËØÓÐÁ½µã£ºTOPºÍNOT IN¡£TOP¿ÉÒÔÌá¸ßÎÒÃǵIJéѯËٶȣ¬¶øNOT IN»á¼õÂýÎÒÃǵIJéѯËٶȣ¬ËùÒÔÒªÌá¸ßÎÒÃÇÕû¸ö·ÖÒ³Ëã·¨µÄËٶȣ¬¾ÍÒª³¹µ×¸ÄÔìNOT IN£¬Í¬ÆäËû·½·¨À´Ìæ´úËü¡£
ÎÒÃÇÖªµÀ£¬¼¸ºõÈκÎ×ֶΣ¬ÎÒÃǶ¼¿ÉÒÔͨ¹ýmax(×Ö¶Î)»òmin(×Ö¶Î)À´Ìáȡij¸ö×Ö¶ÎÖеÄ×î´ó»ò×îСֵ£¬ËùÒÔÈç¹ûÕâ¸ö×ֶβ»Öظ´£¬ÄÇô¾Í¿ÉÒÔÀûÓÃÕâЩ²»Öظ´µÄ×ֶεÄmax»òmin×÷Ϊ·ÖË®Á룬ʹÆä³ÉΪ·ÖÒ³Ëã·¨ÖзֿªÃ¿Ò³µÄ²ÎÕÕÎï¡£ÔÚÕâÀÎÒÃÇ¿ÉÒÔÓòÙ×÷·û“>”»ò“<”ºÅÀ´Íê³ÉÕâ¸öʹÃü£¬Ê¹²éѯÓï¾ä·ûºÏSARGÐÎʽ¡£È磺
Select top 10 * from table1 where id>200
ÓÚÊǾÍÓÐÁËÈçÏ·ÖÒ³·½°¸£º
select top Ò³´óС *
from table1
where id>
(select max (id) from
(select top ((Ò³Âë-1)*Ò³´óС) id from table1 order by id) as T
)
order by id
ÔÚÑ¡Ôñ¼´²»Öظ´Öµ£¬ÓÖÈÝÒ×·Ö±æ´óСµÄÁÐʱ£¬ÎÒÃÇͨ³£»áÑ¡ÔñÖ÷¼ü¡£Ï±íÁгöÁ˱ÊÕßÓÃÓÐ×Å1000ÍòÊý¾ÝµÄ°ì¹«×Ô¶¯»¯ÏµÍ³ÖÐµÄ±í£¬ÔÚÒÔGID£¨GIDÊÇÖ÷¼ü£¬µ«²¢²»ÊǾۼ¯Ë÷Òý¡££©ÎªÅÅÐòÁС¢ÌáÈ¡gid,fariqi,title×ֶΣ¬·Ö±ðÒÔµÚ1¡¢10¡¢100¡¢500¡¢1000¡¢1Íò¡¢10Íò¡¢25Íò¡¢50ÍòҳΪÀý£¬²âÊÔÒÔÉÏÈýÖÖ·ÖÒ³·½°¸µÄÖ´ÐÐËٶȣº£¨µ¥Î»£ººÁÃ룩
Ò³ Âë
·½°¸1
·½°¸2
·½°¸3
1
60
30
76
10
46
16
63
100
1076
720
130
500
540
12943
83
1000
17110
470
250
1Íò
24796
4500
140
10Íò
38326
42283
1553
25Íò
28140
128720
2330
50Íò
121686
127846
7168
´ÓÉϱíÖУ¬ÎÒÃÇ¿ÉÒÔ¿´³ö£¬ÈýÖÖ´æ´¢¹ý³ÌÔÚÖ´ÐÐ100Ò³ÒÔϵķÖÒ³ÃüÁîʱ£¬¶¼ÊÇ¿ÉÒÔÐÅÈεģ¬Ëٶȶ¼ºÜºÃ¡£µ«µÚÒ»ÖÖ·½°¸ÔÚÖ´ÐзÖÒ³1000Ò³ÒÔÉϺó£¬ËٶȾͽµÁËÏÂÀ´¡£µÚ¶þÖÖ·½°¸´óÔ¼ÊÇÔÚÖ´ÐзÖÒ³1ÍòÒ³ÒÔÉϺóËٶȿªÊ¼½µÁËÏÂÀ´¡£¶øµÚÈýÖÖ·½°¸È´Ê¼ÖÕûÓдóµÄ½µÊÆ£¬ºó¾¢ÈÔÈ»ºÜ×ã¡£
ÔÚÈ·¶¨Á˵ÚÈýÖÖ·ÖÒ³·½°¸ºó£¬ÎÒÃÇ¿ÉÒԾݴËдһ¸ö´æ´¢¹ý³Ì¡£´ó¼ÒÖªµÀSQL SERVERµÄ´æ´¢¹ý³ÌÊÇÊÂÏȱàÒëºÃµÄSQLÓï¾ä£¬ËüµÄÖ´ÐÐЧÂÊÒª±Èͨ¹ýWEBÒ³Ãæ´«À´µÄSQLÓï¾äµÄÖ´ÐÐЧÂÊÒª¸ß¡£ÏÂÃæµÄ´æ´¢¹ý³Ì²»½öº¬ÓзÖÒ³·½°¸£¬»¹»á¸ù¾ÝÒ³Ãæ´«À´µÄ²ÎÊýÀ´È·¶¨ÊÇ·ñ½øÐÐÊý¾Ý×ÜÊýͳ¼Æ¡£
-- »ñÈ¡Ö¸¶¨Ò³µÄÊý¾Ý
CREATE PROCEDURE pagination3
@tblName varchar(255), -- ±íÃû
@strGetFields varchar(1000) = '*', -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255)='', -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@doCount bit = 0, -- ·µ»Ø¼Ç¼×ÜÊý, ·Ç 0 ÖµÔò·µ»Ø
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@strWhere varchar(1500) = '' -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where)
AS
declare @strSQL varchar(5000) -- Ö÷Óï¾ä
declare @strTmp varchar(110) -- ÁÙʱ±äÁ¿
declare @strOrder varchar(400) -- ÅÅÐòÀàÐÍ
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--ÒÔÉÏ´úÂëµÄÒâ˼ÊÇÈç¹û@doCount´«µÝ¹ýÀ´µÄ²»ÊÇ0£¬¾ÍÖ´ÐÐ×ÜÊýͳ¼Æ¡£ÒÔϵÄËùÓдúÂ붼ÊÇ@doCountΪ0µÄÇé¿ö
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--Èç¹û@OrderType²»ÊÇ0£¬¾ÍÖ´ÐнµÐò£¬Õâ¾äºÜÖØÒª£¡
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--Èç¹ûÊǵÚÒ»Ò³¾ÍÖ´ÐÐÒÔÉÏ´úÂ룬ÕâÑù»á¼Ó¿ìÖ´ÐÐËÙ¶È
end
else
begin
--ÒÔÏ´úÂ븳ÓèÁË@strSQLÒÔÕæÕýÖ´ÐеÄSQL´úÂë
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
GO
ÉÏÃæµÄÕâ¸ö´æ´¢¹ý³ÌÊÇÒ»¸öͨÓõĴ洢¹ý³Ì£¬Æä×¢ÊÍÒÑдÔÚÆäÖÐÁË¡£
ÔÚ´óÊý¾ÝÁ¿µÄÇé¿öÏ£¬ÌرðÊÇÔÚ²éѯ×îºó¼¸Ò³µÄʱºò£¬²éѯʱ¼äÒ»°ã²»»á³¬¹ý9Ã룻¶øÓÃÆäËû´æ´¢¹ý³Ì£¬ÔÚʵ¼ùÖоͻᵼÖ³¬Ê±£¬ËùÒÔÕâ¸ö´æ´¢¹ý³Ì·Ç³£ÊÊÓÃÓÚ´óÈÝÁ¿Êý¾Ý¿âµÄ²éѯ¡£
±ÊÕßÏ£ÍûÄܹ»Í¨¹ý¶ÔÒÔÉÏ´æ´¢¹ý³ÌµÄ½âÎö£¬Äܸø´ó¼Ò´øÀ´Ò»¶¨µÄÆôʾ£¬²¢¸ø¹¤×÷´øÀ´Ò»¶¨µÄЧÂÊÌáÉý£¬Í¬Ê±Ï£ÍûͬÐÐÌá³ö¸üÓÅÐãµÄʵʱÊý¾Ý·ÖÒ³Ëã·¨¡£
ËÄ¡¢¾Û¼¯Ë÷ÒýµÄÖØÒªÐÔºÍÈçºÎÑ¡Ôñ¾Û¼¯Ë÷Òý
ÔÚÉÏÒ»½ÚµÄ±êÌâÖУ¬±ÊÕßдµÄÊÇ£ºÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì¡£ÕâÊÇÒòΪÔÚ½«±¾´æ´¢¹ý³ÌÓ¦ÓÃÓÚ“°ì¹«×Ô¶¯»¯”ϵͳµÄʵ¼ùÖÐʱ£¬±ÊÕß·¢ÏÖÕâµÚÈýÖÖ´æ´¢¹ý³ÌÔÚСÊý¾ÝÁ¿µÄÇé¿öÏ£¬ÓÐÈçÏÂÏÖÏó£º
1¡¢·ÖÒ³ËÙ¶ÈÒ»°ãά³ÖÔÚ1ÃëºÍ3ÃëÖ®¼ä¡£
2¡¢ÔÚ²éѯ×îºóһҳʱ£¬ËÙ¶ÈÒ»°ãΪ5ÃëÖÁ8Ã룬ÄÄÅ·ÖÒ³×ÜÊýÖ»ÓÐ3Ò³»ò30ÍòÒ³¡£
ËäÈ»ÔÚ³¬´óÈÝÁ¿Çé¿öÏ£¬Õâ¸ö·ÖÒ³µÄʵÏÖ¹ý³ÌÊǺܿìµÄ£¬µ«ÔÚ·ÖÇ°¼¸Ò³Ê±£¬Õâ¸ö1£3ÃëµÄËٶȱÈÆðµÚÒ»ÖÖÉõÖÁûÓо¹ýÓÅ»¯µÄ·ÖÒ³·½·¨ËٶȻ¹ÒªÂý£¬½èÓû§µÄ»°Ëµ¾ÍÊÇ“»¹Ã»ÓÐACCESSÊý¾Ý¿âËٶȿ씣¬Õâ¸öÈÏʶ×ãÒÔµ¼ÖÂÓû§·ÅÆúʹÓÃÄú¿ª·¢µÄϵͳ¡£
±ÊÕ߾ʹ˷ÖÎöÁËһϣ¬ÔÀ´²úÉúÕâÖÖÏÖÏóµÄÖ¢½áÊÇÈç´ËµÄ¼òµ¥£¬µ«ÓÖÈç´ËµÄÖØÒª£ºÅÅÐòµÄ×ֶβ»ÊǾۼ¯Ë÷Òý£¡
±¾ÆªÎÄÕµÄÌâÄ¿ÊÇ£º“²éѯÓÅ»¯¼°·ÖÒ³Ëã·¨·½°¸”¡£±ÊÕßÖ»ËùÒÔ°Ñ“²éѯÓÅ»¯”ºÍ“·ÖÒ³Ëã·¨”ÕâÁ½¸öÁªÏµ²»ÊǺܴóµÄÂÛÌâ·ÅÔÚÒ»Æ𣬾ÍÊÇÒòΪ¶þÕ߶¼ÐèÒªÒ»¸ö·Ç³£ÖØÒªµÄ¶«Î÷¡ª¡ª¾Û¼¯Ë÷Òý¡£
ÔÚÇ°ÃæµÄÌÖÂÛÖÐÎÒÃÇÒѾÌáµ½ÁË£¬¾Û¼¯Ë÷ÒýÓÐÁ½¸ö×î´óµÄÓÅÊÆ£º
1¡¢ÒÔ×î¿ìµÄËÙ¶ÈËõС²éѯ·¶Î§¡£
2¡¢ÒÔ×î¿ìµÄËٶȽøÐÐ×Ö¶ÎÅÅÐò¡£
µÚ1Ìõ¶àÓÃÔÚ²éѯÓÅ»¯Ê±£¬¶øµÚ2Ìõ¶àÓÃÔÚ½øÐзÖҳʱµÄÊý¾ÝÅÅÐò¡£
¶ø¾Û¼¯Ë÷ÒýÔÚÿ¸ö±íÄÚÓÖÖ»Äܽ¨Á¢Ò»¸ö£¬ÕâʹµÃ¾Û¼¯Ë÷ÒýÏԵøü¼ÓµÄÖØÒª¡£¾Û¼¯Ë÷ÒýµÄÌôÑ¡¿ÉÒÔ˵ÊÇʵÏÖ“²éѯÓÅ»¯”ºÍ“¸ßЧ·ÖÒ³”µÄ×î¹Ø¼üÒòËØ¡£
µ«Òª¼Èʹ¾Û¼¯Ë÷ÒýÁмȷûºÏ²éѯÁеÄÐèÒª£¬ÓÖ·ûºÏÅÅÐòÁеÄÐèÒª£¬Õâͨ³£ÊÇÒ»¸öì¶Ü¡£
±ÊÕßÇ°Ãæ“Ë÷Òý”µÄÌÖÂÛÖУ¬½«fariqi£¬¼´Óû§·¢ÎÄÈÕÆÚ×÷ΪÁ˾ۼ¯Ë÷ÒýµÄÆðʼÁУ¬ÈÕÆڵľ«È·¶ÈΪ“ÈÕ”¡£ÕâÖÖ×÷·¨µÄÓŵ㣬ǰÃæÒѾÌáµ½ÁË£¬ÔÚ½øÐл®Ê±¼ä¶ÎµÄ¿ìËÙ²éѯÖУ¬±ÈÓÃIDÖ÷¼üÁÐÓкܴóµÄÓÅÊÆ¡£
µ«ÔÚ·Öҳʱ£¬ÓÉÓÚÕâ¸ö¾Û¼¯Ë÷ÒýÁдæÔÚ×ÅÖظ´¼Ç¼£¬ËùÒÔÎÞ·¨Ê¹ÓÃmax»òminÀ´×îΪ·ÖÒ³µÄ²ÎÕÕÎ½ø¶øÎÞ·¨ÊµÏÖ¸üΪ¸ßЧµÄÅÅÐò¡£¶øÈç¹û½«IDÖ÷¼üÁÐ×÷Ϊ¾Û¼¯Ë÷Òý£¬ÄÇô¾Û¼¯Ë÷Òý³ýÁËÓÃÒÔÅÅÐòÖ®Í⣬ûÓÐÈκÎÓô¦£¬Êµ¼ÊÉÏÊÇÀË·ÑÁ˾ۼ¯Ë÷ÒýÕâ¸ö±¦¹óµÄ×ÊÔ´¡£
Ϊ½â¾öÕâ¸öì¶Ü£¬±ÊÕߺóÀ´ÓÖÌí¼ÓÁËÒ»¸öÈÕÆÚÁУ¬ÆäĬÈÏֵΪgetdate()¡£Óû§ÔÚдÈë¼Ç¼ʱ£¬Õâ¸öÁÐ×Ô¶¯Ð´È뵱ʱµÄʱ¼ä£¬Ê±¼ä¾«È·µ½ºÁÃë¡£¼´Ê¹ÕâÑù£¬ÎªÁ˱ÜÃâ¿ÉÄÜÐÔºÜСµÄÖغϣ¬»¹ÒªÔÚ´ËÁÐÉÏ´´½¨UNIQUEÔ¼Êø¡£½«´ËÈÕÆÚÁÐ×÷Ϊ¾Û¼¯Ë÷ÒýÁС£
ÓÐÁËÕâ¸öʱ¼äÐ;ۼ¯Ë÷ÒýÁÐÖ®ºó£¬Óû§¾Í¼È¿ÉÒÔÓÃÕâ¸öÁвéÕÒÓû§ÔÚ²åÈëÊý¾ÝʱµÄij¸öʱ¼ä¶ÎµÄ²éѯ£¬ÓÖ¿ÉÒÔ×÷ΪΨһÁÐÀ´ÊµÏÖmax»òmin£¬³ÉΪ·ÖÒ³Ëã·¨µÄ²ÎÕÕÎï¡£
¾¹ýÕâÑùµÄÓÅ»¯£¬±ÊÕß·¢ÏÖ£¬ÎÞÂÛÊÇ´óÊý¾ÝÁ¿µÄÇé¿öÏ»¹ÊÇСÊý¾ÝÁ¿µÄÇé¿öÏ£¬·ÖÒ³ËÙ¶ÈÒ»°ã¶¼ÊǼ¸Ê®ºÁÃ룬ÉõÖÁ0ºÁÃë¡£¶øÓÃÈÕÆÚ¶ÎËõС·¶Î§µÄ²éѯËٶȱÈÔÀ´Ò²Ã»ÓÐÈκγٶۡ£
¾Û¼¯Ë÷ÒýÊÇÈç´ËµÄÖØÒªºÍÕä¹ó£¬ËùÒÔ±ÊÕß×ܽáÁËһϣ¬Ò»¶¨Òª½«¾Û¼¯Ë÷Òý½¨Á¢ÔÚ£º
1¡¢Äú×îƵ·±Ê¹Óõġ¢ÓÃÒÔËõС²éѯ·¶Î§µÄ×Ö¶ÎÉÏ£»
2¡¢Äú×îƵ·±Ê¹Óõġ¢ÐèÒªÅÅÐòµÄ×Ö¶ÎÉÏ¡£
Link URL:
http://www.cnblogs.com/tester2test/archive/2006/06/05/417610.html