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

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 Êý¾Ý¿âµÄ


Ïà¹ØÎĵµ£º

SQL Ó¦Óü¸Àý

  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. ......

¾«ÃîSQLÓï¾ä

 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.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§± ......

¶¯Ì¬SQL»ù±¾Óï·¨

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 ......

SQL Serverȡϵͳµ±Ç°Ê±¼ä

getdate //»ñµÃϵͳµ±Ç°ÈÕÆÚ
datepart //»ñÈ¡ÈÕÆÚÖ¸¶¨²¿·Ö£¨ÄêÔÂÈÕʱ·Ö±í£©
getdate()º¯Êý£ºÈ¡µÃϵͳµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØÖµÎªdatetimeÀàÐ͵ġ£
Ó÷¨£ºgetdate()
Àý×Ó£º
select getdate() as dte,dateadd(day,-1,getdate()) as nowdat
Êä³ö½á¹û£º
dte nowdat
--------------------------- ----------------- ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ