Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL SERVERµÄROWCOUNT¹Ø¼ü×Ö

 
ÒòΪÑöÍûORACLE£¬ËùÒÔÒ»Ö±¶¼ÒÔΪSQL SERVERºÜ±¿¡£
¾Ý´«SQL 2005ÓÐÁËRowIDµÄ¶«Î÷£¬¿ÉÒÔ½â¾öTOPÅÅÐòµÄÎÊÌâ¡£¿Éϧ»¹Ã»Óлú»áÌåÑé¡£ÔÚSQL 2000ÖÐд´æ´¢¹ý³Ì£¬×Ü»áÓöµ½ÐèÒªTOPµÄµØ·½£¬¶øÒ»µ©Óöµ½TOP£¬ÒòΪû°ì·¨°ÑTOPºóÃæµÄÊý×Ö×÷Ϊ±äÁ¿Ð´µ½Ô¤±àÒëµÄÓï¾äÖÐÈ¥£¬ËùÒÔÖ»Äܹ»Ê¹Óù¹Ôì SQL£¬Ê¹ÓÃExecÀ´Ö´ÐС£²»ËµÐ§ÂʵÄÎÊÌ⣬ÐÄÀïÒ²×ܾõµÃÕâ¸ö°ì·¨ºÜ±¿¡£
ʵ¼ÊÉÏ£¬ÔÚSQL 2000ÖÐÍêÈ«¿ÉÒÔʹÓÃROWCOUNT¹Ø¼ü×Ö½â¾öÕâ¸öÎÊÌâ¡£
ROWCOUNT¹Ø¼ü×ÖµÄÓ÷¨ÔÚÁª»ú°ïÖúÖÐÓбȽÏÏêϸµÄ˵Ã÷£¬Õâ¶ù¾Í²»ÂÞàÂÁË¡£Ì¸Ì¸Ìå»á¡£
1¡¢Ê¹ÓÃROWCOUNT²éѯǰ¼¸Ðнá¹û¡£
DECLARE @n INT
SET @n = 1000
SET ROWCOUNT @n
SELECT * from Table_1
 
 ÕâÑù£¬²éѯ½á¹û½«µÈͬÓÚ
SELECT TOP 100 from Table_1
2¡¢Í¬ÑùµÄµÀÀí£¬Ê¹ÓÃINSERT INTO..SELECTµÄʱºòÒ²ÓÐЧ¡£
DECLARE @n INT
SET @n = 1000
SET ROWCOUNT @n
INSERT INTO Table_2 (colname1)
SELECT colname1=colname2 from Table_1
Ö´ÐеĽá¹û½«µÈͬÓÚ
INSERT INTO Table_2(colname1)
SELECT TOP 1000 colname1 = colname2 from Table_1
3¡¢Ö´ÐÐUPDATEºÍDELETE¡£
ÒòΪUPDATEºÍDELETEÎÞ·¨Ö±½ÓʹÓÃORDER BYÓï·¨£¬Èç¹ûʹÓÃROWCOUNT£¬½«°´ÕÕÖ÷¼ü˳Ðò´ÓǰÍùºó²Ù×÷¡£
DECLARE @n INT
SET @n = 1000
SET ROWCOUNT @n
DELETE from Table_1
²»¹ýÒ²Óнâ¾ö°ì·¨£¬Ö»ÒªÄܹ»Ê¹ÓÃORDER BY¹Ø¼ü×־ͿÉÒÔÁË£¬±ÈÈç˵ֱ½ÓÓú¬ORDER BYµÄ×Ӿ䣬»òÕßÏÈʹÓÃORDER BYÓï·¨°ÑÐèÒª²Ù×÷µÄ±êʶÁдæÎªÒ»¸öÁÙʱ±í»ò±í±äÁ¿£¬È»ºóÔÙ²Ù×÷Óï¾äÖÐʹÓÃIN»òEXISTS¹Ø¼ü×Ö¡£
DECLARE @n INT
SET @n = 1000
SET ROWCOUNT @n
DECLARE @t TABLE(ID INT)
INSERT INTO @t
SELECT ID from Table_1 ORDER BY colname [ASC/DESC]
DELETE from  Table_1 WHERE ID IN (SELECT ID from @t)
4¡¢¶ÔÓÚROWCOUNTµÄÉèÖÃÊÇÓëSessionÓйصġ£Èç¹ûÕ¼ÓÃÁËÒ»¸öSession£¬ÄÇô¶ÔÓ¦µÄÕâ¸öÊý¾Ý¿âSession½«Ê¹ÓÃ×î½üÒ»´ÎÉèÖõÄROWCOUNT£¬Ö±µ½Session½áÊø»òÕßÐÞ¸ÄÁËROWCOUNT¡£
5¡¢ÔÚÓû§×Ô¶¨Ò庯ÊýÖв»ÄÜʹÓÃROWCOUNT¡£
6¡¢È¡ÏûROWCOUNT¡£
ÎÒ²»ÖªµÀΪʲôÔÚÁª»ú°ïÖúÖÐ˵£¬Ð´´æ´¢¹ý³ÌµÄʱºòÓ¦¸Ã×¢Ò⾡Á¿±ÜÃâʹÓÃROWCOUNT£¬¶ø½¨ÒéʹÓÃTOP¡£ÄѵÀMS²»ÖªµÀTOP¹Ø¼üºóÃæµÄÊý×Ö²»ÄÜΪ±äÁ¿Âð£¿Ò²ÐíMSÊdzöÓÚµ£ÐÄ¿ª·¢ÕßÍü¼ÇÁËÈ¡ÏûROWCOUNT¶øÓ°ÏìÕý³£µÄʵÏÖ¡£
ʹÓÃÕâÑùµÄÓï¾ä¼´¿ÉÈ¡ÏûROWCOUNTÁË¡£
SET ROWCOUNT 0
8¡¢×ܽá
ʹÓÃROWCOUNTÖ®ºó¿ÉÒԺܷ½±ãµÄ½â¾ö


Ïà¹ØÎĵµ£º

sql Êý¾Ý¿âÖв»Í¬Êý¾Ý¿âÖÐÁ½±í´´½¨ÊÓͼ

 CREATE VIEW MYVIEW
AS
   SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
    UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
˵Ã÷£ºÊý¾Ý¿âAµÄ±íµÄ×Ö¶ÎÃû±ØÐëºÍÊý¾Ý¿âBµÄ±íµÄ×Ö¶ÎÃûÏàͬ£¬°üÀ¨Êý¾ÝÀàÐ͵ȡ£ ......

SQLÖÐJOINµÄʹÓÃ


(×¢:outerµÄÒâ˼¾ÍÊÇ"ûÓйØÁªÉϵÄÐÐ"¡£)
1.cross join È«ÍâÁ¬½Ó(µÑ¿¨¶û³Ë»ý)
SELECT A.*, B.* from A FULL OUTER JOIN B ON A.ID = B.ID
2.inner join ÄÚÁ¬½Ó(Ôڵѿ¨¶û³Ë»ýµÄ½á¹û¼¯ÖÐÈ¥µô²»·ûºÏÁ¬½ÓÌõ¼þµÄÐÐ)
SELECT A.* from A INNER JOIN B ON A.ID=B.ID
3.left outer join ×óÍâÁ¬½Ó(ÔÚinner joinµÄ½á¹ ......

linq to sqlÉú³Énot inÓï¾äµÄС¼¼ÇÉ

ÒÔǰһֱ¾õµÃlinq to sqlÉú³ÉÀàËÆwhere id not in (1,3,5)»òwhere id not in (select id from ...)ÕâÑùµÄÌõ¼þ²»ÊǺܷ½±ã£¬Ã¿´ÎÎÒ¶¼ÊǰÑÌõ¼þIDÊÂÏÈÈ¡µ½Ò»¸öÊý×éÀȻºóÓà !Arr.Contains(c.Id)ÕâÑù´¦Àí£¬½ñÌìͻȻ·¢ÏÖÕâÑùºÃɵ£¬Æäʵ¿ÉÒÔÍêȫֱ½ÓÓÃlinqд³ÉÒ»¾ä£¬Ìù¸öʾÀýÔÚÕâÀÒԺ󱸲é
from a in TableA where !(fr ......

SQLÊý¾Ý¿â»¹Ô­Óï¾ä

---¸½¼ÓÊý¾Ý¿â
sp_attach_db   'Êý¾Ý¿âÃû','Êý¾Ý¿âȫ·¾¶','Êý¾Ý¿âÈÕ־ȫ·¾¶'
---²é¿´Êý¾Ý¿âÂß¼­ÎļþÃû
RESTORE FILELISTONLY from disk = '±¸·ÝÎļþ'
---»¹Ô­Êý¾Ý¿â
restore database hzrb from disk = '±¸·ÝÎļþ'
with move 'Ö÷Âß¼­Ãû'     to '´æ·Åmdf·¾¶'    ......

sql serverÖÐinºÍexistsµÄÐ¡Çø±ð oracleδ²âÊÔ

in µÄ»°£¬ Èç¹ûÊÇnull ¾Í²»±È½ÏÁË£¬¼È²»ÊÇin Ò²²»ÊÇ not in
existsµÄ»° ÒòΪÓà = ¼ÓÔÚÌõ¼þÀï±È½ÏÁË£¬ËùÒÔ null ÊÇ not exists
select *
from pricetemp
where cast(ÉÌÆ·¥³ー¥É as varchar(10))not in(
           select shohin_cd
  &nbs ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ