SQL Server µÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
ת×Ô£ºhttp://hi.baidu.com/sonichu/blog/item/a494c4ea276f12dad539c9a8.html
½¨Á¢Ò»¸ö 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 SERV
Ïà¹ØÎĵµ£º
SELECT
(case when a.colorder=1 then d.name else '' end)±íÃû,
a.colorder ×Ö¶ÎÐòºÅ,
a.name ×Ö¶ÎÃû,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity ......
1 --ÏÂÃæµÄ´úÂëÉú³É³¤¶ÈΪ8µÄ±àºÅ£¬±àºÅÒÔBH¿ªÍ·£¬ÆäÓà6λΪÁ÷Ë®ºÅ¡£
2 --µÃµ½Ð±àºÅµÄº¯Êý
3 CREATE FUNCTION f_NextBH()
4 RETURNS char(8)
5 AS
6 BEGIN
7 RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) from tb WITH(XLOCK,PAGLOCK))
8 END
9 GO
10
11 --ÔÚ±íÖÐ ......
תÔØ SQLÖеÄdatetimeÓ÷¨Ïê½â
תÔصØÖ·£ºhttp://blog.sina.com.cn/s/blog_495491770100fq4n.html
2009-07-27 09:45 sql server2000ÖÐʹÓÃconvertÀ´È¡µÃdatetimeÊý¾ÝÀàÐÍÑùʽ
ÈÕÆÚÊý¾Ý¸ñʽµÄ´¦Àí£¬Á½¸öʾÀý£º
CONVERT(varchar(16), ʱ¼äÒ», 20) ½á¹û£º2007-02-01 08:02
ʱ¼äÒ»°ãΪ
getdate()
º¯Êý»òÊý¾Ý±íÀïµÄ ......
ÏÈÕ¾ÔÚÓ¦ÓóÌÐòµÄ½Ç¶È˵˵ËüÃǵIJ»Í¬¡£
1¡¢ Ö±½ÓÆ´ SQL
¾ÍÏñ´ó¼ÒÁ˽âµÄÄÇÑù£¬Ö±½ÓÆ´ SQL ´øÀ´ÁË SQL ×¢Èë¹¥»÷£¬´øÀ´ÁËƴʱЩÐíµÄÐÔÄÜËðʧ£¬µ«ÊÇÆ´²»ÓÃÌí¼Ó SqlParameter £¬»áÉÙдºÜ¶à´úÂë——ºÜ¶àÈËϲ»¶Ö±½ÓÆ´£¬Ò²Ðí¾ÍÒòΪÕâµã¡£ÕâÖÖ×ö·¨»á°ÑÄãÆ´ºÃµÄ SQL ÔÑùÖ±½Ó·¢Ë͵½ DB ·þÎñÆ÷È¥Ö´ÐС££¨×¢ÒâÀàËÆ ......
½ñÌì×öSQL ÓÅ»¯£¬²éÕÒÖ´Ðмƻ®Ê±£¬Ö´Ðмƻ®£¬·¢ÏÖ´ËÖ´Ðмƻ®ÓëÒÔÍùµÄ¼Æ»®ÓÐËùÇø±ð£»ÕÒ¼»¥ÁªÍø£¬ÖÕÓÚÕÒһƪÓйØÑо¿±È½ÏÉîÈëµÄÎÄÕ£»
ÔÖ´Ðмƻ®Ê¹ÓõÄÊÇË÷ÒýɨÃ裬ͻȻһÏ»áʹÓÃË÷Òý¸²¸Ç¼¼Êõ£¬Ð§ÂÊ´óÔö£»
SELECT * µÄÕæÏࣺË÷Òý¸²¸Ç(index coverage)
SELECT *µÄЧÂʺÜÔã¸âÂ𣿵±È»£¬ËùÓÐÈ˶¼ÖªµÀÕâÒ»µã£¬µ«ÊÇΪʲôÄØ£ ......