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

SQL Server 2005ÖеÄT SQLÔöÇ¿(3) TOP OUTPUT

TOP ÔöÇ¿¹¦ÄÜ
1¡¢TOP ÔöÇ¿¡£¿ÉÒÔÖ¸¶¨Ò»¸öÊý×Ö±í´ïʽ£¬ÒÔ·µ»ØÒªÍ¨¹ý²éѯӰÏìµÄÐÐÊý»ò°Ù·Ö±È£¬»¹¿ÉÒÔ¸ù¾ÝÇé¿öʹÓñäÁ¿»ò×Ó²éѯ¡£
¿ÉÒÔÔÚDELETE¡¢UPDATEºÍINSERT²éѯÖÐʹÓÃTOPÑ¡Ïî¡£
2¡¢¸üºÃµØÌæ»»SET ROWCOUNTÑ¡Ïʹ֮¸üΪÓÐЧ¡£
OUTPUT
1¡¢SQL Server 2005ÒýÈëÒ»¸öеÄOUTPUT×Ӿ䣬ÒÔʹÄú¿ÉÒÔ³åÐÞ¸ÄÓï¾ä(INSERT¡¢UPDATE¡¢DELETE)Öн«Êý¾Ý·µ»Øµ½±í±äÁ¿ÖС£
2¡¢ÐµÄOUTPUT×Ó¾ÖµÄÓ﷨Ϊ£º
OUTPUT INTO @table_variable
¿ÉÒÔͨ¹ýÒýÓòåÈëµÄ±í»òɾ³ýµÄ±íÀ´·ÃÎʱ»Ð޸ĵÄÐеľÉ/ÐÂÓ°Ïó£¬Æä·½Ê½Óë·ÃÎÊ´¥·¢Æ÷ÀàËÆ¡£ÔÚINSERTÓï¾äÖУ¬Ö»ÄÜ·ÃÎʲåÈëµÄ±í¡£ÔÚDELETEÓï¾äÖУ¬Ö»ÄÜ·ÃÎÊɾ³ýµÄ±í¡£ÔÚUPDATEÓï¾äÖУ¬¿ÉÒÔ·ÃÎʲåÈëµÄ±íºÍɾ³ýµÄ±í¡£
3¡¢´úÂëÑÝʾ
USE demo
GO
CREATE TABLE tt
(
id INT IDENTITY,
c1 VARCHAR(15)
)
GO
INSERT INTO tt VALUES ('r1')
INSERT INTO tt VALUES ('r2')
INSERT INTO tt VALUES ('r5')
INSERT INTO tt VALUES ('r6')
INSERT INTO tt VALUES ('r7')
INSERT INTO tt VALUES ('r8')
INSERT INTO tt VALUES ('r9')
INSERT INTO tt VALUES ('r10')
DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
DELETE tt
OUTPUT DELETED.id, DELETED.c1 INTO @del
WHERE id < 3
SELECT * from @del
GO
-----------------------------------------------
USE demo
GO
CREATE TABLE toptest (column1 VARCHAR(150))
GO
INSERT INTO toptest VALUES('t1')
INSERT INTO toptest VALUES('t2')
INSERT INTO toptest VALUES('t3')
INSERT INTO toptest VALUES('t4')
INSERT INTO toptest VALUES('t5')
INSERT INTO toptest VALUES('t6')
INSERT INTO toptest VALUES('t7')
INSERT INTO toptest VALUES('t8')
SELECT * from toptest
GO
CREATE TABLE toptest2 (column2 VARCHAR(150))
GO
INSERT INTO toptest2 VALUES('c1')
INSERT INTO toptest2 VALUES('c2')
--ÉùÃ÷3¸ö±äÁ¿
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT
--¸³Öµ
SET @a = 10
SET @b = 5
SELECT @c = @a/@b
--ʹÓüÆËã±í´ïʽ
SELECT TOP(@c) * from toptest
--ʹÓÃSELECTÓï¾ä×÷ΪÌõ¼þ
SELECT TOP(SELECT COUNT(*) from toptest2) *
from toptest
--Ö¸³ötop
DELETE TOP(2) toptest where column1>'t6'
--¸üÐÂtop
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'


Ïà¹ØÎĵµ£º

[SQL Server] ÈÕÖ¾¹ÜÀí(´ýÐø)

 --±¸·ÝÊÂÎñÈÕÖ¾ (×ÔÉÏÒ»´Î±¸·ÝÒÔÀ´£¬ÖÁµ±Ç°µÄÒÑÍê³ÉµÄÊÂÎñµÄÈÕÖ¾)
backup log MyDBName to disk='F:\dbbak\GamePoint\MyDBName_log_2010052709.bak';
 
--½Ø¶ÏÈÕÖ¾(½Ø¶Ïµôµ±Ç°µÄÒÑÍê³ÉµÄÊÂÎñµÄÈÕÖ¾), SQL Server 2005, 2008ûÓÐÕâ¸öÑ¡Ïî.
backup log MyDBName with no_Log;
 
--ÊÕËõÈÕÖ¾(ÖÁ1M,µ ......

Çå¿ÕSQL ServerÊý¾Ý¿âÖÐËùÓбíÊý¾ÝµÄ·½·¨

Æäʵɾ³ýÊý¾Ý¿âÖÐÊý¾ÝµÄ·½·¨²¢²»¸´ÔÓ£¬ÎªÊ²Ã´ÎÒ»¹Òª¶à´ËÒ»¾ÙÄØ£¬Ò»ÊÇÎÒÕâÀï½éÉܵÄÊÇɾ³ýÊý¾Ý¿âµÄËùÓÐÊý¾Ý£¬ÒòΪÊý¾ÝÖ®¼ä¿ÉÄÜÐγÉÏà»¥Ô¼Êø¹ØÏµ£¬É¾³ý²Ù×÷¿ÉÄÜÏÝÈëËÀÑ­»·£¬¶þÊÇÕâÀïʹÓÃÁË΢ÈíδÕýʽ¹«¿ªµÄsp_MSForEachTable´æ´¢¹ý³Ì¡£
¡¡¡¡Ò²ÐíºÜ¶à¶ÁÕßÅóÓѶ¼¾­Àú¹ýÕâÑùµÄÊÂÇ飺ҪÔÚ¿ª·¢Êý¾Ý¿â»ù´¡ÉÏÇåÀíÒ»¸ö¿Õ¿â£¬µ«ÓÉÓÚ¶ÔÊ ......

sql_map_config.xml(SQL Map»·¾³Îļþ)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
 "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
 <!-- ÅäÖúÍÓÅ»¯SQL Map½âÎöʵÀýµÄÉ趨 -->
 <settings cacheModelsEnabled=" ......

sql¹¤¾ß

×î½üÔÚÓÃsqlcmd¹¤¾ß½øÐÐÊý¾Ý±¸·Ý£¬½ñÌìÔÚ»»µ½sql2000ʱͻȻ²»ÄÜÓá£Ô­À´2000ÊÇûÓÐÕâ¸ö¹¤¾ßµÄ£º
sql2005:
sqlcmd -S ·þÎñÆ÷Ãû»ò·þÎñÆ÷µØÖ· -U Óû§ -P ÃÜÂë -i "ÒýÓõÄsqlÓï¾äÎļþ"
sql2000:¿ÉÒÔÓÃosql´úÌæ£¬¹¦ÄÜÒ»Ñù
osql -S ·þÎñÆ÷Ãû»ò·þÎñÆ÷µØÖ· -U Óû§ -P ÃÜÂë -i "ÒýÓõÄsqlÓï¾äÎļþ" ......

Sql server With

with HostDevice as (----ÉÌ»§Ö÷»ú
select TerminalID ,Deviceid hostDeviceid ,Device.ModelID,Device.SN HostSN,Device.MerchantID,Device.InstallAddress,Device.SoftVersion
from Device
join model HostM on Device.ModelID=HostM.ModelID and HostM.Category in(0,3,4,5,6,8)
--where TerminalID is not null
) ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ