SQL Server Ë÷Òý½á¹¹¼°ÆäʹÓã¨ËÄ£©
×÷Õߣºfreedk
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¶þ¡¢¸ÄÉÆSQLÓï¾ä
Èý¡¢ÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì
¾Û¼¯Ë÷ÒýµÄÖØÒªÐÔºÍÈçºÎÑ¡Ôñ¾Û¼¯Ë÷Òý
¡¡¡¡ÔÚÉÏÒ»½ÚµÄ±êÌâÖУ¬±ÊÕßдµÄÊÇ£ºÊµÏÖСÊý¾ÝÁ¿ºÍº£Á¿Êý¾ÝµÄͨÓ÷ÖÒ³ÏÔʾ´æ´¢¹ý³Ì¡£ÕâÊÇÒòΪÔÚ½«±¾´æ´¢¹ý³ÌÓ¦ÓÃÓÚ“°ì¹«×Ô¶¯»¯”ϵͳµÄʵ¼ùÖÐʱ£¬±ÊÕß·¢ÏÖÕâµÚÈýÖÖ´æ´¢¹ý³ÌÔÚСÊý¾ÝÁ¿µÄÇé¿öÏ£¬ÓÐÈçÏÂÏÖÏó£º
1¡¢·ÖÒ³ËÙ¶ÈÒ»°ãά³ÖÔÚ1ÃëºÍ3ÃëÖ®¼ä¡£
2¡¢ÔÚ²éѯ×îºóһҳʱ£¬ËÙ¶ÈÒ»°ãΪ5ÃëÖÁ8Ã룬ÄÄÅ·ÖÒ³×ÜÊýÖ»ÓÐ3Ò³»ò30ÍòÒ³¡£
¡¡¡¡ËäÈ»ÔÚ³¬´óÈÝÁ¿Çé¿öÏ£¬Õâ¸ö·ÖÒ³µÄʵÏÖ¹ý³ÌÊǺܿìµÄ£¬µ«ÔÚ·ÖÇ°¼¸Ò³Ê±£¬Õâ¸ö1£3ÃëµÄËٶȱÈÆðµÚÒ»ÖÖÉõÖÁûÓо¹ýÓÅ»¯µÄ·ÖÒ³·½·¨ËٶȻ¹ÒªÂý£¬½èÓû§µÄ»°Ëµ¾ÍÊÇ“»¹Ã»ÓÐACCESSÊý¾Ý¿âËٶȿ씣¬Õâ¸öÈÏʶ×ãÒÔµ¼ÖÂÓû§·ÅÆúʹÓÃÄú¿ª·¢µÄϵͳ¡£
¡¡¡¡±ÊÕ߾ʹ˷ÖÎöÁËһϣ¬ÔÀ´²úÉúÕâÖÖÏÖÏóµÄÖ¢½áÊÇÈç´ËµÄ¼òµ¥£¬µ«ÓÖÈç´ËµÄÖØÒª£ºÅÅÐòµÄ×ֶβ»ÊǾۼ¯Ë÷Òý£¡
¡¡¡¡±¾ÆªÎÄÕµÄÌâÄ¿ÊÇ£º“²éѯÓÅ»¯¼°·ÖÒ³Ëã·¨·½°¸”¡£±ÊÕßÖ»ËùÒÔ°Ñ“²éѯÓÅ»¯”ºÍ“·ÖÒ³Ëã·¨”ÕâÁ½¸öÁªÏµ²»ÊǺܴóµÄÂÛÌâ·ÅÔÚÒ»Æ𣬾ÍÊÇÒòΪ¶þÕ߶¼ÐèÒªÒ»¸ö·Ç³£ÖØÒªµÄ¶«Î÷¨D¨D¾Û¼¯Ë÷Òý¡£
ÔÚÇ°ÃæµÄÌÖÂÛÖÐÎÒÃÇÒѾÌáµ½ÁË£¬¾Û¼¯Ë÷ÒýÓÐÁ½¸ö×î´óµÄÓÅÊÆ£º
1¡¢ÒÔ×î¿ìµÄËÙ¶ÈËõС²éѯ·¶Î§¡£
2¡¢ÒÔ×î¿ìµÄËٶȽøÐÐ×Ö¶ÎÅÅÐò¡£
¡¡¡¡µÚ1Ìõ¶àÓÃÔÚ²éѯÓÅ»¯Ê±£¬¶øµÚ2Ìõ¶àÓÃÔÚ½øÐзÖҳʱµÄÊý¾ÝÅÅÐò¡£
¡¡¡¡¶ø¾Û¼¯Ë÷ÒýÔÚÿ¸ö±íÄÚÓÖÖ»Äܽ¨Á¢Ò»¸ö£¬ÕâʹµÃ¾Û¼¯Ë÷ÒýÏԵøü¼ÓµÄÖØÒª¡£¾Û¼¯Ë÷ÒýµÄÌôÑ¡¿ÉÒÔ˵ÊÇʵÏÖ“²éѯÓÅ»¯”ºÍ“¸ßЧ·ÖÒ³”µÄ×î¹Ø¼üÒòËØ¡£
¡¡¡¡µ«Òª¼Èʹ¾Û¼¯Ë÷ÒýÁмȷûºÏ²éѯÁеÄÐèÒª£¬ÓÖ·ûºÏÅÅÐòÁеÄÐèÒª£¬Õâͨ³£ÊÇÒ»¸öì¶Ü¡£±ÊÕßÇ°Ãæ“Ë÷Òý”µÄÌÖÂÛÖУ¬½«fariqi£¬¼´Óû§·¢ÎÄÈÕÆÚ×÷ΪÁ˾ۼ¯Ë÷ÒýµÄÆðʼÁУ¬ÈÕÆڵľ«È·¶ÈΪ“ÈÕ”¡£ÕâÖÖ×÷·¨µÄÓŵ㣬ǰÃæÒѾÌáµ½ÁË£¬ÔÚ½øÐл®Ê±¼ä¶ÎµÄ¿ìËÙ²éѯÖУ¬±ÈÓÃIDÖ÷¼üÁÐÓкܴóµÄÓÅÊÆ¡£
¡¡¡¡µ«ÔÚ·Öҳʱ£¬ÓÉÓÚÕâ¸ö¾Û¼¯Ë÷ÒýÁдæÔÚ×ÅÖظ´¼Ç¼£¬ËùÒÔÎÞ·¨Ê¹ÓÃmax»òminÀ´×îΪ·ÖÒ³µÄ²ÎÕÕÎ½ø¶øÎÞ·¨ÊµÏÖ¸üΪ¸ßЧµÄÅÅÐò¡£¶øÈç¹û½«IDÖ÷¼üÁÐ×÷Ϊ¾Û¼¯Ë÷Òý£¬ÄÇô¾Û¼¯Ë÷Òý³ýÁËÓÃÒÔÅÅÐòÖ®Í⣬ûÓÐÈκÎÓô¦£¬Êµ¼ÊÉÏÊÇÀË·ÑÁ˾ۼ¯Ë÷ÒýÕâ¸ö±¦¹óµÄ×ÊÔ´¡£
¡¡¡¡Îª½â¾öÕâ¸öì¶Ü£¬±ÊÕߺóÀ´ÓÖÌí¼ÓÁËÒ»¸öÈÕÆÚÁУ¬ÆäĬÈÏֵΪgetdate()¡£Óû§ÔÚдÈë¼Ç¼ʱ£¬Õâ¸öÁÐ×Ô¶¯Ð´È뵱ʱµÄʱ¼ä£¬Ê±¼ä¾«È·µ½ºÁÃë¡£¼´Ê¹ÕâÑù£¬ÎªÁ˱
Ïà¹ØÎĵµ£º
°æȨÉùÃ÷£º×ªÔØʱÇëÒÔ³¬Á´½ÓÐÎʽ±êÃ÷ÎÄÕÂÔʼ³ö´¦ºÍ×÷ÕßÐÅÏ¢¼°±¾ÉùÃ÷
http://copyzhu.blogbus.com/logs/10168078.html
À´Ô´£ºÐ¡ÕÅ.NET
½¨Á¢±í£º
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName ......
ijÌìÓиöͬÊÂÎÊÎÒÔõôÔÚÒ»¸ö±íÖвéÕÒij×Ö¶ÎֵΪnull²¢°ÑËüÌ滻ΪÁíÒ»Öµ¡£
¿ªÊ¼ÎÒ¾õµÃÓà select * from ±íÃû where ×Ö¶Î=null Ó¦¸Ã¿ÉÒԵġ£
µ«½á¹ûÊÇûÓб¨´í£¬µ«Ê²Ã´Ò²Ã»ÓÐÏÔʾ³öÀ´¡£
ºóÀ´ÓÖÊÔ£º¡¡
select * from ±íÃû where len(×Ö¶Î)=0 ½á¹ûÈÔÊÇʲôҲûÓÐÏÔʾ¡£
ÎÞÒâÖУ¬Óá¡¡¡select * from ±íÃû wh ......
--Óû§Óë½ÇÉ«¹Øϵ
select a.uid as uid,a.status as uStatus,a.name as uName,
b.uid as rId,b.status as rStatus,b.name as rName
from sysusers a inner join sysusers b on a.gid = b.uid
where a.issqlrole = 0 and a.isapprole = 0 and a.hasdbaccess = 1 and (b.issqlrole = 1 or b.isapprole = 1)
......
Ò»¡¢ÉîÈëdz³öÀí½âË÷Òý½á¹¹
¡¡¡¡Êµ¼ÊÉÏ£¬Äú¿ÉÒÔ°ÑË÷ÒýÀí½âΪһÖÖÌØÊâµÄĿ¼¡£Î¢ÈíµÄSQL SERVERÌṩÁËÁ½ÖÖË÷Òý£º¾Û¼¯Ë÷Òý£¨clustered index£¬Ò²³Æ¾ÛÀàË÷Òý¡¢´Ø¼¯Ë÷Òý£©ºÍ·Ç¾Û¼¯Ë÷Òý£¨nonclustered index£¬Ò²³Æ·Ç¾ÛÀàË÷Òý¡¢·Ç´Ø¼¯Ë÷Òý£©¡£ÏÂÃ棬ÎÒÃǾÙÀýÀ´ËµÃ÷һϾۼ¯Ë÷ÒýºÍ·Ç¾Û¼¯Ë÷ÒýµÄÇø±ð£º
¡¡¡¡Æäʵ£¬ÎÒÃǵĺºÓï×Öµäµ ......
SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨
2009-12-10 22:16
¹Ì¶¨ÈÕÆÚ¼õÒ»¸öÔ£¨ÈÕ¡¢Ä꣩
dateadd(±í´ïʽ1£¬±í´ïʽ2£¬±í´ïʽ3£©
±í´ïʽ1ΪMM»òYY»òDD
±í´ïʽ2Ϊ1
±í´ïʽ3ΪҪ¼õµÄÈÕÆÚ
Ò»¸öÔµĵÚÒ»Ìì
µÚÒ»¸öÀý×Ó£¬ÎÒ½«¸æËßÄãÈçºÎ´Óµ±Ç°ÈÕÆÚÈ¥Õâ¸öÔµÄ×îºóÒ»Ìì¡£Çë×¢Ò⣺Õâ¸öÀý×ÓÒÔ¼°ÕâƪÎÄÕÂÖеÄÆäËûÀý×Ó¶¼½«Ö»Ê¹ÓÃDATEDIFF ......