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 Êý¾Ý¿âµÄ sysobjects ±íÖеÄÁÙʱ±í£¬ÆäÈ«ÃûÓÉ CREATE TABLE Óï¾äÖÐÖ¸¶¨µÄ±íÃûºÍϵͳÉú³ÉµÄÊý×Öºó׺×é³É¡£ÎªÁËÔÊÐí×·¼Óºó׺£¬Îª±¾µØÁÙʱ±íÖ¸¶¨µÄ±íÃû table_name ²»Äܳ
Ïà¹ØÎĵµ£º
ÔÚSQL SERVERÖУ¬Äã¿ÉÄÜÐèÒª»ñµÃµ±Ç°ÈÕÆںͼÆËãһЩÆäËûµÄÈÕÆÚ£¬ÀýÈ磬ÄãµÄ³ÌÐò¿ÉÄÜÐèÒªÅжÏÒ»¸öÔµĵÚÒ»Ìì»òÕß×îºóÒ»Ìì¡£ÄãÃǴ󲿷ÖÈË´ó¸Å¶¼ÖªµÀÔõÑù°ÑÈÕÆÚ½øÐзָÄê¡¢Ô¡¢Èյȣ©£¬È»ºó½ö½öÓ÷ָî³öÀ´µÄÄê¡¢Ô¡¢ÈյȷÅÔÚ¼¸¸öº¯ÊýÖмÆËã³ö×Ô¼ºËùÐèÒªµÄÈÕÆÚ£¡ÔÚÕâƪÎÄÕÂÀÎÒ½«¸æËßÄãÈçºÎʹÓÃDATEADDºÍDATEDIFFº¯ÊýÀ´¼ÆË ......
Global.asaxÎļþÖÐ
ÔÚÒ³ÃæÌøתʱ£¬·ÀÖ¹´«ÖµµÄ²ÎÊýÖÐSQL×¢È룺
void Application_BeginRequest(object sender, EventArgs e)
{
ProcessRequest();
}
void ProcessRequest()
{
try
......
²éѯǰ¼¸Ìõ¼Ç¼SQLÔÚ²»Í¬Êý¾Ý¿âÖеÄÓ÷¨
1. ORACLE
SELECT * from TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * from TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
»òÕß
SELECT COLUMN from TABLE FETCH FIRST N ROWS ONLY
4. SQL SERVER
SE ......
editplusĬÈÏÊÇûÓÐsqlÓï·¨¸ßÁÁµÄ£¬ÔÒòÊÇËüµÄÄÚ²¿Ã»ÓÐsql.stxµÄÕâÑùÒ»¸öÓï·¨Îļþ
ÎÒÃÇ×Ô¼ºÔÚ EditPlus µÄ°²×°Ä¿Â¼ÏÂÃæн¨Ò»¸öÎļþÃûΪsql.stx
ÀïÃæµÄÄÚÈÝÊÇ£º
#TITLE=SQL
; SQL syntax file written by KK.
#DELIMITER=,(){}[]-+*%/="'~!&|<>?:;.
#QUOTATION1='
#QUOTATION2="
#LINECOMMENT=--
#COMME ......
ËøµÄ¸ÅÊö ¡¡¡¡Ò». ΪʲôҪÒýÈëËø
¡¡¡¡¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌ⣺
¡¡¡¡¶ªÊ§¸üÐÂ
¡¡¡¡A£¬BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐ޸ģ¬ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û£¬±ÈÈ綩Ʊϵͳ
¡¡¡¡Ôà¶Á
¡¡¡¡AÓû§ÐÞ¸ÄÁËÊý¾Ý£¬ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý£¬µ«AÓû§ÒòΪijЩÔÒòÈ¡ÏûÁ˶ÔÊý¾ ......