SQL游標學(xué)習游標一般格式：DECLARE 游標名稱(chēng) CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...OPEN 游標名稱(chēng)FETCH NEXT FROM 游標名稱(chēng) INTO 變量名1,變量名2,變量名3,...WHILE @@FETCH_STATUS=0 BEGIN SQL語(yǔ)句執行過(guò)程... ... FETCH NEXT FROM 游標名稱(chēng) INTO 變量名1,變量名2,變量名3,... ENDCLOSE 游標名稱(chēng)DEALLOCATE 游標名稱(chēng)例子：/*
功能：數據庫表格tbl_users數據
deptid userid username
1 100 a
1 101 b
2 102 c
要求用一個(gè)sql語(yǔ)句輸出下面結果
deptid username1 ab
2 c
[要求用游標實(shí)現]
設計： OK_008
時(shí)間： 2006-05
備注：無(wú)*/
create table #Temp1(deptid int,userid int,username varchar(20)) --待測試的數據表create table #Temp2(deptid int,username varchar(20)) --結果表--先把一些待測試的數據插入到待測試表#Temp1中insert into #Temp1select 1,100,‘a(chǎn)‘ union allselect 1,101,‘b‘ union allselect 1,131,‘d‘ union allselect 1,201,‘f‘ union allselect 2,302,‘c‘ union all select 2,202,‘a(chǎn)‘ union allselect 2,221,‘e‘ union allselect 3,102,‘y‘ union all select 3,302,‘e‘ union allselect 3,121,‘t‘ --declare @deptid int,@username varchar(20)--定義游標declare Select_cursor cursor for select deptid,username from #Temp1open Select_cursorfetch next from Select_cursor into @deptid,@username --提取操作的列數據放到局部變量中while @@fetch_status=0 --返回被 FETCH 語(yǔ)句執行的最后游標的狀態(tài)/*@@FETCH_STATUS =0 FETCH 語(yǔ)句成功@@FETCH_STATUS =-1 FETCH 語(yǔ)句失敗或此行不在結果集中@@FETCH_STATUS =-2 被提取的行不存在*/ begin --當表#Temp2列deptid存在相同的數據時(shí)，就直接在列username上追加@username值 if(exists(select * from #Temp2 where deptid=@deptid )) update #Temp2 set username=username +@username where deptid=@deptid else --插入新數據 insert into #Temp2 select @deptid,@username fetch next from Select_cursor into @deptid,@username endclose Select_cursor deallocate Select_cursorselect * from #Temp2 --測試結果Drop table #Temp1,#Temp2[
本帖最后由 DVD 于 2006-12-14 17:08 編輯 ]
作者: DVD
時(shí)間: 2006-12-14 16:36
自動(dòng)生成表的更新數據的存儲過(guò)程
設計原因：在數據庫設計中，有時(shí)候建立了很多表，每個(gè)表都有Insert、Update、Delete結構基本相同的存儲，要是能有個(gè)自動(dòng)生成表的更新數據的存儲過(guò)程，就方便了我們不必浪費時(shí)間去寫(xiě)每一張表的Insert、Update、Delete存儲過(guò)程。
設計方法：先提取表的各字段信息，包含字段的數據類(lèi)型、數據定義長(cháng)度、是否主鍵等。再根據提取出來(lái)的信息構造成表的更新數據的存儲過(guò)程。下面的方法是有一個(gè)用戶(hù)自定義函數FN_GetObjColInfo和一個(gè)存儲過(guò)程SP_CreateProcdure來(lái)實(shí)現。
用戶(hù)自定義函數FN_GetObjColInfo：/*功能：返回某一表的所有字段、存儲過(guò)程、函數的參數信息設計：OK_008時(shí)間：2006-05*/CREATE FUNCTION FN_GetObjColInfo(@ObjName varchar(50))RETURNS @Return_Table TABLE( TName nvarchar(50), TypeName nvarchar(50), TypeLength nvarchar(50), Colstat Bit ) AS BEGIN INSERT @Return_Table /* 主要是從系統表中提取表(對象)的各字段信息。 sysobjects: 在數據庫內創(chuàng )建的每個(gè)對象（約束、默認值、日志、規則、存儲過(guò)程等）在表中占一行 syscolumns:每個(gè)表和視圖中的每列在表中占一行，存儲過(guò)程中的每個(gè)參數在表中也占一行 systypes: 保存數據類(lèi)型和用戶(hù)定義數據類(lèi)型 */ select b.name as 字段名,c.name as 字段類(lèi)型,b.length/2 as 字段長(cháng)度,b.colstat as 是否自動(dòng)增長(cháng) from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on c.xusertype=b.xtype where a.name =@ObjName order by B.ColID RETURNENDGO存儲過(guò)程SP_CreateProcdure：CREATE PROCEDURE SP_CreateProcdure@TableName nvarchar(50)AS/*功能： 自動(dòng)生成表的更新數據的存儲過(guò)程 如：當建立表MyTable后，執行SP_CreateProcdure ，生成表MyTable的數據更 新的存儲過(guò)程UP_MyTable設計： OK_008時(shí)間： 2006-05備注： 1、請在查詢(xún)分析器上執行：EXEC SP_CreateProcdure TableName 2、由于生成的字符串長(cháng)度合計很多時(shí)候存在>4000以上，所有只使用Print輸出， 再Copy即可。 3、該方法能生成一般表的更新數據的存儲過(guò)程，其中更新格式可以根據實(shí)際 情況修改。設計方法： 1、提取表的各個(gè)字段信息 2、 ──┰─ 構造更新數據過(guò)程 ├─ 構造存儲過(guò)程參數部分 ├─ 構造新增數據部分 ├─ 構造更新數據部分 ├─ 構造刪除數據部分 3、分段PRINT 4、把輸出來(lái)的結果復制到新建立存儲過(guò)程界面中即可使用。*/DECLARE @strParameter nvarchar(3000)DECLARE @strInsert nvarchar(3000)DECLARE @strUpdate nvarchar(3000)DECLARE @strDelete nvarchar(500)DECLARE @strWhere nvarchar(100)DECLARE @strNewID nvarchar(100)DECLARE @SQL_CreateProc nvarchar(4000)SET @SQL_CreateProc=‘CREATE PROCEDURE UP_‘+@TableName +char(13)+‘@INTUpdateID int,‘ +‘ /* -1 刪除 0 修改 1新增 */‘SET @strParameter=‘‘SET @strInsert=‘‘SET @strUpdate=‘‘SET @strWhere=‘‘DECLARE @TName nvarchar(50),@TypeName nvarchar(50),@TypeLength nvarchar(50),@Colstat bitDECLARE Obj_Cursor CURSOR FOR SELECT * FROM FN_GetObjColInfo(@TableName)OPEN Obj_CursorFETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@ColstatWHILE @@FETCH_STATUS=0 BEGIN --構造存儲過(guò)程參數部分 SET @strParameter=@strParameter +CHAR(13)+‘@‘+ @TName + ‘ ‘ +@TypeName+ (CASE WHEN @TypeName=‘nvarchar‘ THEN ‘(‘+@TypeLength+‘)‘ ELSE ‘‘ END)+‘,‘ --構造新增數據部分 IF @Colstat=0 SET @strInsert=@strInsert + ‘@‘+ @TName +‘,‘ --構造更新數據部分 IF (@strWhere=‘‘) BEGIN IF @Colstat=0 SET @strNewID=‘SET @‘+@TName+‘=(Select ISNULL(MAX(‘+@TName+‘),0) From ‘+@TableName+‘)+1 --取新的ID‘ SET @strWhere=‘ WHERE ‘+@TName+‘=‘+‘@‘+@TName END ELSE SET @strUpdate=@strUpdate+@TName+‘=‘+‘@‘+@TName +‘,‘ --構造刪除數據部分 FETCH NEXT FROM Obj_Cursor INTO @TName,@TypeName,@TypeLength,@Colstat ENDCLOSE Obj_CursorDEALLOCATE Obj_CursorSET @strParameter=LEFT(@strParameter,LEN(@strParameter)-1) --去掉最右邊的逗號SET @strUpdate=LEFT(@strUpdate,LEN(@strUpdate)-1) SET @strInsert=LEFT(@strInsert,LEN(@strInsert)-1) --存儲過(guò)程名、參數PRINT @SQL_CreateProc+@strParameter +CHAR(13)+‘AS‘--修改PRINT ‘IF (@INTUpdateID=0)‘PRINT‘ BEGIN‘+CHAR(13)PRINT CHAR(9)+‘UPDATE ‘+@TableName+‘ SET ‘+@strUpdate+CHAR(13)+CHAR(9)+@strWherePRINT ‘ END‘--增加PRINT ‘ELSE IF (@INTUpdateID=1)‘PRINT ‘ BEGIN‘PRINT CHAR(9)+@strNewIDPRINT CHAR(9)+‘INSERT INTO ‘+@TableName+‘ (‘+REPLACE(@strInsert,‘@‘,‘‘) +‘) VALUES ( ‘+@strInsert +‘)‘PRINT ‘ END‘--刪除PRINT ‘ELSE‘PRINT ‘ BEGIN‘PRINT CHAR(9)+‘DELETE FROM ‘+@TableName +@strWherePRINT ‘ END‘PRINT ‘GO‘GO[
本帖最后由 DVD 于 2006-12-14 16:39 編輯 ]
作者: DVD
時(shí)間: 2006-12-14 16:40
繪畫(huà)日歷
設計原因：記得那時(shí)，周末沒(méi)事干隨便想到的，只是為了學(xué)習。設計方法：先構造一個(gè)月的日歷圖，再使用循環(huán)繪畫(huà)1-12月的日歷圖。/*功能：繪畫(huà)日歷設計：OK_008時(shí)間：2006-05*/DECLARE @Year nvarchar(4)DECLARE @YearMonth nvarchar(7) --月份DECLARE @strTop nvarchar(200)DECLARE @ForI INT,@ForYear INT ,@MaxDay INTDECLARE @RowX INT --行位置 DECLARE @strWeekDayList nvarchar(20)DECLARE @strPrint nvarchar(300)-- ======================================SET @Year=‘2006‘ --請在這里輸入年份-- ======================================--設置日歷上邊的標題格式SET @strTop= ‘日‘+char(9)+‘一‘+char(9)+‘二‘ +char(9)+‘三‘++char(9)+‘四‘++char(9)+‘五‘++char(9)+‘六‘ +char(13)+ ‘───────────────────────────‘--設置星期列表SET @strWeekDayList=‘日一二三四五六‘SET @ForYear=1WHILE @ForYear<=12 --1月份至12月份BEGIN --取當月格式 SET @YearMonth=@Year + ‘-‘ +CAST( @ForYear AS nvarchar(2)) --取當月的最大日期 SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+‘-01‘))) --找出1號的開(kāi)始位置 SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+‘-01‘),1),@strWeekDayList)-1 SET @strPrint=‘‘ SET @ForI=1 WHILE @ForI<=@RowX --構造1號的位置，并繪畫(huà)空白處 BEGIN SET @strPrint=@strPrint+CHAR(9) SET @ForI=@ForI+1 END SET @ForI=1 WHILE @ForI<=@MaxDay --構造2號到月底的位置，并繪畫(huà) BEGIN SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9) SET @RowX=@RowX+1 SET @ForI=@ForI+1 IF (@RowX%7=0) --滿(mǎn)一個(gè)星期就換行 BEGIN SET @RowX=0 SET @strPrint=@strPrint+CHAR(13) END END SET @ForYear=@ForYear+1 -- 打印輸出一個(gè)月的結果 PRINT ‘━━━━━━━━━━━━━━━━━━━━━━━━━━━‘ PRINT +Char(9)++Char(9)+‘ ‘+@YearMonth+CHAR(10) PRINT @strTop PRINT @strPrint +CHAR(10) END執行結果：[
本帖最后由 DVD 于 2006-12-14 16:48 編輯 ]
附件:
繪制結果圖.jpg (2006-12-14 16:47, 32.08 K) / 該附件被下載次數 1
http://bbs.54master.com/attachment.php?aid=31905
作者: DVD
時(shí)間: 2006-12-14 16:49
一個(gè)金額轉化的問(wèn)題
設計原因：記得是有一客戶(hù)的特別要求，開(kāi)始有點(diǎn)暈，后來(lái)還是能做好。/*功能：把Money類(lèi)型轉換成nvarchar類(lèi)型,保留三位小數，而且把數值后面多余的0去掉。 如money類(lèi)型的數值2.59100，轉化成nvarchar(20)類(lèi)型時(shí)，要求的結果是2.591 如money類(lèi)型的數值89.7800，轉化成nvarchar(20)類(lèi)型時(shí)，要求的結果是89.78設計：OK_008時(shí)間：2006-07*/CREATE TABLE #Temp([ID] int ,F1 nvarchar(20),F2 nvarchar(20),SMoney money,Dmoney money)INSERT INTO #Temp SELECT 1,‘F1‘,‘F2‘,45.895,23.89000UNION SELECT 2,‘F2‘,‘F3‘,45.895,234.67UNION SELECT 3,‘F3‘,‘F4‘,25.835,32.123UNION SELECT 4,‘F4‘,‘F5‘,13.7600,31.6754UNION SELECT 5,‘F5‘,‘F6‘,34.783,78.345/*要求的結果如下：SMoney +‘X‘+ Dmoney ------------------------------------------------------45.895X23.8945.895X234.6725.835X32.12313.76X31.67534.783X78.345*/SELECT *,cast(SMoney as nvarchar(20)) FROM #Temp/* 經(jīng)過(guò)3次的數據才可以把money類(lèi)型的數據后邊多余的零給去掉， money先轉換成decimal,再轉換成float,然后是nvarchar*/SELECT CAST(CAST(CAST(SmoneyAS decimal(20,3)) AS float) as nvarchar)+‘X‘+ CAST(CAST(CAST(DmoneyAS decimal(20,3)) AS float)AS nvarchar)FROM #TempDROP TABLE #Temp
作者: DVD
時(shí)間: 2006-12-14 16:50
多個(gè)變量賦值問(wèn)題.
設計原因：記得在itpub上有一朋友問(wèn)到這個(gè)問(wèn)題，感覺(jué)頂有意思的，順便拿過(guò)來(lái)。/*=================================原來(lái)的問(wèn)題是這樣的：=================================現在的表結構是這樣的ID ReferID ReferCount1 0 22 1 NULL3 2 NULL4 3 1然后聲明四個(gè)變量declare @t1 int declare @t2 intdeclare @t3 intdeclare @t4 int然后使用四條記錄中的ReferCount為值四個(gè)變量賦值問(wèn)題是能不能使用一條sql語(yǔ)句完成，用case行不行的請高手們指教*/--解決方法：CREATE TABLE #Temp(ID int,ReferID int,ReferCount int)INSERT INTO #Temp SELECT 1, 0, 2 UNION ALLSELECT 2, 1, NULL UNION ALLSELECT 3, 2, NULL UNION ALLSELECT 4, 3, 1DECLARE @t1 int DECLARE @t2 intDECLARE @t3 intDECLARE @t4 int--根據ID的唯一性來(lái)查詢(xún)SELECT @t1=CASE ID WHEN 1 THEN ReferCount ELSE @t1 END ,@t2=CASE ID WHEN 2 THEN ReferCount ELSE @t2 END ,@t3=CASE ID WHEN 3 THEN ReferCount ELSE @t3 END ,@t4=CASE ID WHEN 4 THEN ReferCount ELSE @t4 END FROM #TempSELECT @t1 AS ‘@t1‘,@t2 AS ‘@t2‘,@t3 AS ‘@t3‘,@t4 AS ‘@t4‘DROP TABLE #Temp/*運行結果：@t1|@t2|@t3|@t4--------------------2 |NULL|1 |NULL*/
作者: DVD
時(shí)間: 2006-12-14 16:52
計算在一段時(shí)間內某周幾（如星期一）的所有日期
設計方法：先提取第1個(gè)星期一，然后就是循環(huán)+7。難點(diǎn)就是怎么樣提取第1個(gè)星期一。
/*功能: 計算在某一段時(shí)間內某周幾（如星期一）的所有日期設計：OK_008時(shí)間：2006-10*/DECLARE @Date datetimeDECLARE @StartDate datetimeDECLARE @EndDate datetimeDECLARE @WeekDay intDECLARE @i intSET DATEFIRST 7 --設置每周的第一天 SET @StartDate=‘2006-01-01‘ --統計的開(kāi)始日期SET @EndDate=‘2006-12-31‘ --統計的結束日期SET @WeekDay=2 --根據實(shí)際的@@DATEFIRST而定，一般默認是7，如 @StartDate=‘2006-01-01‘時(shí)候， @WeekDay=3表示星期二SET @i=DATEPART(weekday,@StartDate)PRINT ‘每周的第1天設置@@DATEFIRST: ‘+CAST(@@DATEFIRST AS nvarchar(1))PRINT ‘開(kāi)始日期對應一周的第幾天: ‘+CAST(@i AS nvarchar(1))IF(@i<=@WeekDay AND @i<7) SET @i=@WeekDay-@i ELSE IF(@i<=@WeekDay AND @i=7) SET @i=@i-@WeekDayELSE SET @i=@@DATEFIRST-@i+@WeekDaySET @Date=DATEADD(day,@i,@StartDate)WHILE @Date<=@EndDate BEGIN IF(@StartDate<=@Date) PRINT CONVERT(nvarchar(10),@Date,121) SET @Date=DATEADD(Week,1,@Date) ENDGO/* ==============運行結果================*//*每周的第1天設置@@DATEFIRST: 7開(kāi)始日期對應一周的第幾天: 12006-01-022006-01-09... ...2006-12-182006-12-25*/
作者: DVD
時(shí)間: 2006-12-14 16:52
字符串的分割
/*功能：實(shí)現字符串的分割，達到批量提交數據參數和分批處理功能。設計：wgh時(shí)間：2006-09*/declare @str1 nvarchar(100) --要分割的字符串declare @str2 nvarchar(100) --分割得的子字符串declare @split nvarchar(40) --分割字符or字符串declare @patIndex int --分割字符or字符串第一次出現的起始位置set @split=‘★■->‘set @str1=‘334,345,‘‘dfd‘‘,★■->select * from Employee★■->45654DFG★■->452897★■->97887657★■->123445‘set @str2=‘‘--從左至右分割while @str1<>@str2 begin set @patIndex=patindex(‘%‘+@split+‘%‘,@str1) --patindex函數返回@str1中@split第一次出現的起始位置 if @patIndex>0 set @str2=left(@str1,@patIndex-1) --截取@str1中以@split為分割的左邊的字符串 else set @str2=@str1 print @str2 --調試/* 執行過(guò)程 ....*/ if @patIndex>0 set @str1=right(@str1,len(@str1)-len(@str2)-len(@split)) --截斷字符串，為的是下一次循環(huán)能取得，@str1中@split第一次出現的起始位置end
作者: DVD
時(shí)間: 2006-12-14 16:54
查找某一數據庫沒(méi)有主鍵的所有用戶(hù)表
/*功能：查找所有(某一)數據庫沒(méi)有主鍵的所有用戶(hù)表設計：wgh時(shí)間：2006-06*//*設計方法：使用游標的方法，從系統表sysobjects、syscolumns、sysindexes提取表的相關(guān)信息。*/DECLARE @DatabaseName nvarchar(20)DECLARE @Execute_Sql nvarchar(4000)SET @DatabaseName=‘ydhr‘ --要是要檢查所有數據庫就SET @DatabaseName=‘‘CREATE table #Temp(DatabaseName nvarchar(20),TableName nvarchar(50))IF ISNULL(@DatabaseName,‘‘)=‘‘ DECLARE cursor_Sql CURSOR FOR SELECT name FROM master..sysdatabases WHERE dbid>=7 --dbid>=7 都是屬于用戶(hù)建立的數據庫ELSE DECLARE cursor_Sql CURSOR FOR SELECT name FROM master..sysdatabases WHERE dbid>=7 AND name=@DatabaseNameOPEN cursor_SqlFETCH NEXT FROM cursor_Sql INTO @DatabaseNameWHILE @@FETCH_STATUS=0 BEGIN SET @Execute_Sql=‘INSERT INTO #Temp SELECT ‘‘‘+@DatabaseName+‘‘‘,A.name FROM ‘+@DatabaseName+‘..sysobjects AS A WHERE OBJECTPROPERTY(A.id, N‘‘IsUserTable‘‘) = 1 AND NOT EXISTS( SELECT 1 FROM ‘+@DatabaseName+‘..sysindexes AS i INNER JOIN ‘+@DatabaseName+‘..sysindexkeys k ON i.id = k.id AND i.indid = k.indid INNER JOIN ‘+@DatabaseName+‘..sysobjects AS o ON i.id = o.id INNER JOIN ‘+@DatabaseName+‘..syscolumns c on i.id=c.id and k.colid = c.colid WHERE o.xtype = ‘‘U‘‘ AND A.id=i.id AND EXISTS(SELECT 1 FROM ‘+@DatabaseName+‘..sysobjects WHERE xtype = ‘‘PK‘‘ AND name = i.name) )‘ /* OBJECTPROPERTY（）返回當前數據庫中對象的有關(guān)信息,OBJECTPROPERTY(對象id, N‘‘IsUserTable‘‘) 表示是否為用戶(hù)表 xtype=‘U‘ 在系統表sysobjects中表示查詢(xún)的對象屬于用戶(hù)表，與OBJECTPROPERTY(對象id, N‘‘IsUserTable‘‘)功能一致，只是用法格式不同 xtype=‘PK‘ PRIMARY KEY 約束（類(lèi)型是 K） */ EXECUTE(@Execute_Sql) PRINT @Execute_Sql --為了能更清楚語(yǔ)句的含義，我這里可以使用PRINT查看整條執行語(yǔ)句 FETCH NEXT FROM cursor_Sql INTO @DatabaseName ENDCLOSE cursor_SqlDEALLOCATE cursor_SqlSELECT * FROM #TempDROP TABLE #Temp
作者: DVD
時(shí)間: 2006-12-14 16:55
重命名數據庫、表、列
重命名數據庫、表、列名，使用系統存儲過(guò)程sp_rename是最簡(jiǎn)單的了。
我們先來(lái)看看幫助文檔的說(shuō)明：
sp_rename
更改當前數據庫中用戶(hù)創(chuàng )建對象（如表、列或用戶(hù)定義數據類(lèi)型）的名稱(chēng)。
語(yǔ)法
sp_rename [ @objname = ] ‘object_name‘ ,
[ @newname = ] ‘new_name‘
[ , [ @objtype = ] ‘object_type‘ ]
@objname 對象名
@objtype 對象類(lèi)型 | 值 | 描述 |
COLUMN
| 要重命名的列。
|
DATABASE
| 用戶(hù)定義的數據庫。要重命名數據庫時(shí)需用此選項。
|
INDEX
| 用戶(hù)定義的索引。
|
OBJECT
| 在 sysobjects 中跟蹤的類(lèi)型的項目。例如，OBJECT 可用來(lái)重命名約束（CHECK、FOREIGN KEY、PRIMARY/UNIQUE KEY）、用戶(hù)表、視圖、存儲過(guò)程、觸發(fā)器和規則等對象。
|
USERDATATYPE
| 通過(guò)執行 sp_addtype 而添加的用戶(hù)定義數據類(lèi)型。
|
/*功能：重命名數據庫、表、列編寫(xiě)：wgh時(shí)間：2006-11*/--重新命名數據庫IF EXISTS(SELECT 1 FROM master..sysdatabases WHERE name=‘BBS‘) EXECUTE sp_rename @objname=‘BBS‘,@newname=‘BBS1‘,@objtype=‘DATABASE‘--重新命名表名IF EXISTS(SELECT 1 FROM sysobjects WHERE name=‘bbs_log‘ AND OBJECTPROPERTY(id,‘IsUserTable‘)=1) EXECUTE sp_rename @objname=‘bbs_log‘,@newname=‘bbs_log1‘,@objtype=‘OBJECT‘--重新命名列名IF EXISTS(SELECT 1 FROM syscolumns WHERE id=OBJECT_ID(‘bbs_log‘) AND name=‘ColumnName‘ AND OBJECTPROPERTY(id,‘IsUserTable‘)=1) EXECUTE sp_rename @objname=‘bbs_log.[ColumnName]‘,@newname=‘NewColumnName‘,@objtype=‘COLUMN‘
作者: DVD
時(shí)間: 2006-12-14 16:56
格式化字符串 ，如輸入01 自動(dòng)生成 0000001
/*=================================================== 功能：格式化字符串 ，如輸入01 自動(dòng)生成 0000001 備注：當位數達到10以上,要另寫(xiě)算法,否則出錯 設計：weiguohao 日期：2006-03-11 ===================================================*/CREATE FUNCTION FORMAT_STR(@strX Nvarchar(20),@FormatLength INT)RETURNS NVARCHAR(20) AS BEGIN DECLARE @Return NVARCHAR(20)DECLARE @Length INT SET @Length=LEN(@strX) IF @Length>=@FormatLength SET @Return=@strX ELSE BEGIN -- 格式化，只要是調用到數學(xué)函數POWER，其他就不再說(shuō)明，太簡(jiǎn)單了 SET @Return=CAST(RIGHT(POWER(10,@FormatLength),@FormatLength-@Length) AS NVARCHAR(20))+@strX ENDRETURN @ReturnEND
作者: DVD
時(shí)間: 2006-12-14 16:57
一個(gè)廠(chǎng)家與銷(xiāo)售商的查詢(xún)問(wèn)題問(wèn)題來(lái)來(lái)自itpub論壇上的一位朋友，如下：多個(gè)廠(chǎng)家多個(gè)銷(xiāo)售商每個(gè)廠(chǎng)家對任一銷(xiāo)售商都免費前10次的貨款有廠(chǎng)家表，銷(xiāo)售表，銷(xiāo)售商表想求得銷(xiāo)售商三個(gè)表拼合一起的視圖視圖中有個(gè)字段標出免費的銷(xiāo)售記錄？廠(chǎng)家表：ID ，名稱(chēng)銷(xiāo)售表：ID，銷(xiāo)售單號廠(chǎng)家：ID，銷(xiāo)售商ID
銷(xiāo)售商：ID，銷(xiāo)售商名稱(chēng)預得視圖：銷(xiāo)售單ID，廠(chǎng)家ID，銷(xiāo)售商ID，是否免費謝謝哥哥姐姐了。幫忙寫(xiě)寫(xiě)…… 解答：CREATE TABLE #Suppliers(SupplierID int,CompanyName nvarchar(40)) --廠(chǎng)家表
CREATE TABLE #Sell(SellID int,SupplierID int,CustomerID int) --銷(xiāo)售表
CREATE TABLE #Customers(CustomerID int,CompanyName nvarchar(40)) --銷(xiāo)售商、客戶(hù)表
INSERT INTO #Suppliers
SELECT 1,N‘Supplier1‘ UNION ALL
SELECT 2,N‘Supplier2‘
INSERT INTO #Customers
SELECT 1,N‘Customer1‘ UNION ALL
SELECT 2,N‘Customer2‘ INSERT INTO #Sell
SELECT 1,1,1 UNION ALL
SELECT 2,1,1 UNION ALL
SELECT 3,1,1 UNION ALL
SELECT 4,1,1 UNION ALL
SELECT 5,1,1 UNION ALL
SELECT 6,1,1 UNION ALL
SELECT 7,1,1 UNION ALL
SELECT 8,1,2 UNION ALL
SELECT 9,1,2 UNION ALL
SELECT 10,1,2 UNION ALL
SELECT 11,1,2 UNION ALL
SELECT 12,1,2 UNION ALL
SELECT 13,1,2 UNION ALL
SELECT 14,1,2 UNION ALL
SELECT 15,2,1 UNION ALL
SELECT 16,2,1 UNION ALL
SELECT 17,2,2 UNION ALL
SELECT 18,2,2 UNION ALL
SELECT 18,2,2
--每個(gè)廠(chǎng)家對任一銷(xiāo)售商都免費前3次的貨款（根據實(shí)際修改提前多少次是免費的貨款）--主要是使用到一個(gè)GROUP BY ...HAVING ...在對數據進(jìn)行分組和聚合后，就會(huì )用到 HAVING 子句中的條件
SELECT A.*,(CASE WHEN EXISTS(SELECT 1 FROM #Sell AS B WHERE A.SupplierID=B.SupplierID AND A.CustomerID=B.CustomerID AND B.SellID<=A.SellID GROUP BY B.CustomerID HAVING COUNT(1)<=3 ) THEN ‘是‘ ELSE ‘否‘ END) AS ‘是否免費‘ FROM #Sell AS A
DROP TABLE #Suppliers,#Sell,#Customers結果[
本帖最后由 DVD 于 2006-12-14 17:01 編輯 ]
附件:
圖.JPG (2006-12-14 17:00, 31.84 K) / 該附件被下載次數 0
http://bbs.54master.com/attachment.php?aid=31906
作者: 綠竹居
時(shí)間: 2006-12-14 16:59
俺的天。好多。俺要好好學(xué)習。。謝謝DVD
作者: DVD
時(shí)間: 2006-12-14 17:01
不用游標就可以實(shí)現的一個(gè)問(wèn)題
原來(lái)問(wèn)題如下：如果有2個(gè)表T1（aid,bid），T2（bid,bname,bprice）
T1的字段表示顧客ID和商品ID，一個(gè)顧客可以買(mǎi)多種商品現在T1有記錄:
1,10
1,11
1,12
2,10
2,13
T2的字段表示商品ID，商品名稱(chēng)，商品價(jià)格現在T2有記錄：
10,aaa,1
11,bbb,2
12,ccc,3
13,ddd,4
現在要實(shí)現顯示顧客買(mǎi)的商品名稱(chēng)和價(jià)錢(qián)即字段：
aid,bname,bprice
但是同一個(gè)顧客只保留一個(gè)ID，其他為null請問(wèn)怎么實(shí)現？即將上面的記錄實(shí)現為：
1,aaa,1
,bbb,2
,ccc,3
2,aaa,1
,ddd,4
如果不用游標有辦法實(shí)現嗎？解決方法：CREATE TABLE #T1(aid int ,bid int)
INSERT INTO #T1
SELECT 1,10 UNION ALL
SELECT 1,11 UNION ALL
SELECT 1,12 UNION ALL
SELECT 2,10 UNION ALL
SELECT 2,13
CREATE TABLE #T2(bid int,bname nvarchar(10),bprice int)
INSERT INTO #T2
SELECT 10,‘a(chǎn)aa‘,1 UNION ALL
SELECT 11,‘bbb‘,2 UNION ALL
SELECT 12,‘ccc‘,3 UNION ALL
SELECT 13,‘ddd‘,4
SELECT A.aid,B.bname,B.bprice INTO #T3 FROM #T1 AS A LEFT OUTER JOIN #T2 AS B ON A.bid=B.bid
DECLARE @aid int ,@lastaid intUPDATE #T3 SET @aid=(CASE WHEN ISNULL(@lastaid,‘‘) =aid THEN NULL ELSE aid END),aid=@aid,@lastaid=aid
SELECT * FROM #T3
DROP TABLE #T1,#T2,#T3結果：/*
1 aaa 1
NULL bbb 2
NULL ccc 3
2 aaa 1
NULL ddd 4
*/
作者: DVD
時(shí)間: 2006-12-14 17:02
一個(gè)數據替換問(wèn)題
原來(lái)的問(wèn)題：
我要將([4000]+[3900])/([3100]+[3200]+[3900])*[3900]替換成
(0+0)/(0+0+0)*0
就是把[4000]樣的替換成０
解決方法如下，
建立一個(gè)自定義函數：CREATE FUNCTION CreateMathExpression
(@ColumnName nvarchar(500))
RETURNS nvarchar(1000) AS
BEGIN
DECLARE @FieldValue nvarchar(20)
DECLARE @Return nvarchar(1000)
DECLARE @Sql nvarchar(1000)
DECLARE @Index int
DECLARE @End bit
SET @End=0
SET @Return=‘‘
--SET @ColumnName=‘[3900]/222.8*([3200]/[3900])‘
SET @Index=CHARINDEX(‘[‘,@ColumnName)
WHILE @Index<>0
BEGIN
IF @End=0
BEGIN
SET @Index=CHARINDEX(‘[‘,@ColumnName)
IF @Index=0 GOTO Exit_While
SET @Return=@Return+LEFT(@ColumnName,@Index-1)
SET @End=1
END
ELSE
BEGIN
SET @Index=CHARINDEX(‘]‘,@ColumnName)
SET @FieldValue=LEFT(@ColumnName,@Index-1)
SET @Return=@Return+ ‘0‘
SET @End=0
END
SET @ColumnName=RIGHT(@ColumnName,len(@ColumnName)-@Index)
END
Exit_While:
SET @Return=@Return+@ColumnName
RETURN @Return
END--------------------------------------------------------------------例子:DECLARE @N nvarchar(200)
SET @N=‘([4000]+[3900])/([3100]+[3200]+[3900])*[3900]‘
SELECT @N
SELECT dbo.CreateMathExpression(@N)
-------------------------結果:
(0+0)/(0+0+0)*0備注：函數中的一些代碼是別的用途,可以忽略。
作者: DVD
時(shí)間: 2006-12-14 17:04
向表中一列插入1-100的連續數
在BBS上看到一朋友問(wèn)到這樣的一個(gè)問(wèn)題：求助：向表中一列插入1-100的連續數
table A 中有BH字段（INT），怎么連續給BH賦值1-100,象下面的一樣
BH
------------------------------------------------
1
2
.
.
.
100根據常規的方法，我們會(huì )想到使用While來(lái)實(shí)現以上的功能。如，一朋友所寫(xiě)的這樣：CREATE TABLE TestTable(BH INT)
GO
SET NOCOUNT ON/*SET NOCOUNT { ON | OFF } 當 SET NOCOUNT 為 ON 時(shí)，不返回計數（表示受 Transact-SQL 語(yǔ)句影響的行數）。當 SET NOCOUNT 為 OFF 時(shí)，返回計數*/
GO
DECLARE @MyCounter INT
SET @MyCounter = 1
WHILE (@MyCounter < 101)
BEGIN
INSERT INTO TestTable VALUES
(@MyCounter
)
SET @MyCounter = @MyCounter + 1
END
GO
SET NOCOUNT OFF
GO
select * from TestTable
--drop table TestTable我看一下，突然想到一個(gè)使用變量的方法來(lái)實(shí)現，如下：
DECLARE @i intCREATE TABLE #Temp ([id] int, C1 nvarchar(20),BH int)SET @i=400WHILE @i<=500BEGININSERT INTO #Temp SELECT @i,CAST(@i+rand() as nvarchar(20)),nullSET @i=@i+1ENDSELECT * FROM #Temp--更新字段BH,更新行數為前100行SET @i=0 --@i相當于For循環(huán)中的i,每更新一條記錄設置加@i=@i+1UPDATE #Temp SET @i=@i+1 ,BH=@i WHERE [id] IN(SELECT TOP 100 [id] FROM #Temp)SELECT * FROM #TempDROP TABLE #Temp
