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. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
3. ......
1.°´ÐÕÊϱʻÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.·ÖÒ³SQLÓï¾ä
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøÎ»ÖÃ
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§± ......
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 ......
getdate //»ñµÃϵͳµ±Ç°ÈÕÆÚ
datepart //»ñÈ¡ÈÕÆÚÖ¸¶¨²¿·Ö£¨ÄêÔÂÈÕʱ·Ö±í£©
getdate()º¯Êý£ºÈ¡µÃϵͳµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØÖµÎªdatetimeÀàÐ͵ġ£
Ó÷¨£ºgetdate()
Àý×Ó£º
select getdate() as dte,dateadd(day,-1,getdate()) as nowdat
Êä³ö½á¹û£º
dte nowdat
--------------------------- ----------------- ......