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