SQL SERVERÁÙʱ±íµÄʹÓÃ
drop table #Tmp --ɾ³ýÁÙʱ±í#Tmp
create table #Tmp --´´½¨ÁÙʱ±í#Tmp
(
ID int IDENTITY (1,1) not null, --´´½¨ÁÐID,²¢ÇÒÿ´ÎÐÂÔöÒ»Ìõ¼Ç¼¾Í»á¼Ó1
WokNo varchar(50),
primary key (ID) --¶¨ÒåIDΪÁÙʱ±í#TmpµÄÖ÷¼ü
);
Select * from #Tmp --²éѯÁÙʱ±íµÄÊý¾Ý
truncate table #Tmp --Çå¿ÕÁÙʱ±íµÄËùÓÐÊý¾ÝºÍÔ¼Êø
Ïà¹ØÀý×Ó£º
Declare @Wokno Varchar(500) --ÓÃÀ´¼Ç¼ְ¹¤ºÅ
Declare @Str NVarchar(4000) --ÓÃÀ´´æ·Å²éѯÓï¾ä
Declare @Count int --Çó³ö×ܼǼÊý
Declare @i int
Set @i = 0
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count
Begin
Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
Select @WokNo,@i --Ò»ÐÐÒ»ÐаÑÖ°¹¤ºÅÏÔʾ³öÀ´
Set @i = @i + 1
End
ÁÙʱ±í
¿ÉÒÔ´´½¨±¾µØºÍÈ«¾ÖÁÙʱ±í¡£±¾µØÁÙʱ±í½öÔÚµ±Ç°»á»°Öпɼû£»È«¾ÖÁÙʱ±íÔÚËùÓлỰÖж¼¿É¼û¡£
±¾µØÁÙʱ±íµÄÃû³ÆÇ°ÃæÓÐÒ»¸ö±àºÅ·û (#table_name)£¬¶øÈ«¾ÖÁÙʱ±íµÄÃû³ÆÇ°ÃæÓÐÁ½¸ö±àºÅ·û (##table_name)¡£
SQL Óï¾äʹÓà CREATE TABLE Óï¾äÖÐΪ table_name Ö¸¶¨µÄÃû³ÆÒýÓÃÁÙʱ±í£º
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
Èç¹û±¾µØÁÙʱ±íÓÉ´æ´¢¹ý³Ì´´½¨»òÓɶà¸öÓû§Í¬Ê±Ö´ÐеÄÓ¦ÓóÌÐò´´½¨£¬Ôò SQL Server ±ØÐëÄܹ»Çø·ÖÓɲ»Í¬Óû§´´½¨µÄ±í¡£Îª´Ë£¬SQL Server ÔÚÄÚ²¿ÎªÃ¿¸ö±¾µØÁÙʱ±íµÄ±íÃû×·¼ÓÒ»¸öÊý×Öºó׺¡£´æ´¢ÔÚ tempdb Êý¾Ý¿âµÄ
Ïà¹ØÎĵµ£º
1 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
¡¡¡¡Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØϵÊÇÓÅ»¯¹ØϵÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
¡¡¡¡±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØϵµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿É ......
1 :ÆÕͨSQLÓï¾ä¿ÉÒÔÓÃexecÖ´ÐÐ
Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- Çë×¢Òâ×Ö·û´®Ç°Ò»¶¨Òª¼ÓN
2:×Ö¶ÎÃû£¬±íÃû£¬Êý¾Ý¿âÃûÖ®Àà×÷Ϊ±äÁ¿Ê±£¬±ØÐëÓö¯Ì¬SQL
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tab ......
ÓÐЩʱºòÎÒÃÇÐèÒª²éѯÊý¾Ý¿âÖеÄʱ¼ä×ֶΣ¬ÀýÈç2009-11-11 11:11:11:111 ÕâÑùµÄʱ¼ä¸ñʽ¡£
¶øÎÒÃÇÓÐЩʱºò²»ÓðÑÕû¸öµÄ×ֶβéѯ³öÀ´£¬ÐèÒª°ÑÇ°ÃæµÄÈÕÆÚ½ØÈ¡³öÀ´£¬»òÕ߰ѺóÃæµÄʱ¼ä½ØÈ¡³öÀ´¡£
Õâ¸öʱºò¾ÍÒªÓõ½SQLÀïÃæµÄʱ¼äº¯ÊýÁË£º
select convert(char(10),×Ö¶ÎÃû,108) from ±íÃû
ÉÏÊöÓï¾äÊǽ«ºóÃæµÄʱ¼ä²éѯ³öÀ´£¬¸ñ ......
sp_databases --Áгö·þÎñÆ÷ÉϵÄËùÓÐÊý¾Ý¿â
sp_server_info --Áгö·þÎñÆ÷ÐÅÏ¢£¬Èç×Ö·û¼¯£¬°æ±¾ºÍÅÅÁÐ˳Ðò
sp_stored_procedures--Áгöµ±Ç°»·¾³ÖеÄËùÓд洢¹ý³Ì
sp_tables --Áгöµ±Ç°»·¾³ÖÐËùÓпÉÒÔ²éѯµÄ¶ÔÏó
sp_start_job --Á¢¼´Æô¶¯×Ô¶¯»¯ÈÎÎñ
sp_stop_job --Í£Ö¹ÕýÔÚÖ´ÐеÄ×Ô¶¯»¯ÈÎÎñ
sp_password --Ì ......
SqlÓï¾ä
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb) SQL:select * into bfrom awhere 1<>1;
2. ˵Ã÷£º¿½±´± ......