关闭

游标应用

189人阅读 评论(0) 收藏 举报

create database testtest
use testtest

create table 表1
(ID int,
name varchar(10),
qq varchar(10),
phone varchar(20)
)
  
  insert into 表1 values(1,'秦云','10102800','13500000')
  insert into 表1 values(2,'秦云','10102800','13500000')
  insert into 表1 values(2,'在路上','10378','13600000')
  
  insert into 表1 values(3   ,'LEO'     ,'10000'        ,'13900000')
  
create table 表2
(
ID int,
NAME varchar(10) ,
上机时间 datetime,
管理员 varchar(10)
)
  
insert into 表2 values(1,'秦云'   ,cast('2004-1-1' as datetime),'李大伟')

insert into 表2 values(2,'秦云'   ,cast('2005-1-1' as datetime),'马化腾')

insert into 表2 values (3,'在路上' ,cast('2005-1-1' as datetime),'马化腾')

insert into 表2 values(4,'秦云'   ,cast('2005-1-1' as datetime),'李大伟')

insert into 表2 values(5,'在路上' ,cast('2005-1-1' as datetime),'李大伟')
  
  
create function GetNameStr(@name nvarchar(10))
returns nvarchar(800)
as
begin
declare @nameStr nvarchar(800)
declare @tempStr nvarchar(800)
declare @flag int
declare myCur cursor for ( select 管理员 from 表2 where 表2.NAME = @name )
open myCur
fetch next from myCur into @tempStr
set @flag = 0
while @@fetch_status = 0
begin
if @flag = 0
begin
set @nameStr = @tempStr
end
else
begin
set @nameStr = @nameStr + ',' + @tempStr
end
set @flag = @flag + 1
fetch next from myCur into @tempStr
end
close myCur
deallocate myCur
return @nameStr
end

select 表2.NAME as 姓名, count(ID) as 上机次数, dbo.GetNameStr(表2.NAME) as 管理员
from 表2
where 表2.NAME in ( select 表1.NAME from 表1 )
group by 表2.NAME
  
  select * from 表1
  select * from 表2

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:71328次
    • 积分:1216
    • 等级:
    • 排名:千里之外
    • 原创:54篇
    • 转载:1篇
    • 译文:0篇
    • 评论:6条