sql Replace,cursor

来源：互联网发布：李宁淘宝官网编辑：程序博客网时间：2024/05/20 20:05
SQL
declare @name varchar(20)= 'ceo bicycle'
print replace(@name,'bicycle','ceo')
代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->例子：/*功能：数据库表格tbl_users数据deptid userid username         100      a     101      b     102      c要求用一个sql语句输出下面结果deptid username       ab       c[要求用游标实现设计： OK_008时间： 2006-05备注：无*/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' 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' 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 语句执行的最后游标的状态/*@@FETCH_STATUS =0          FETCH 语句成功@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中@@FETCH_STATUS =-2 被提取的行不存在*/        begin                  --当表#Temp2列deptid存在相同的数据时，就直接在列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
0 0