SQL Server中自定义函数和游标应用的经典案例

80酷酷网    80kuku.com

  server|函数|游标

   这是网友的问题,我当时立马给出了自己的解决方案,但是没有想到中间有点小问题,发现后经过自己仔细调试,完全得到正确结果后,那个网友已经结帖了。我的代码遂成为鸡肋,食之无味,弃之可惜。但是我觉得我的代码确实还是挺经典的,所以整理了一下,供各位网友欣赏。
问题: 
 

假设环境如下:

    表1:      ID, NAME,      QQ,     PHONE,

表中数据:      1       秦云        10102800 13500000

                2       在路上      10378    13600000

                3       LEO         10000    13900000

 

    表2:      ID, NAME,  上机时间,管理员,

表中数据:     1   秦云    2004-1-1  李大伟

               2   秦云    2005-1-1  马化腾

               3    在路上  2005-1-1  马化腾

               4    秦云   2005-1-1  李大伟

               5   在路上 2005-1-1  李大伟

 

实现目的:从表1中取所有人员列表,从表2中取上机次数和管理员.

             上机人员名单    上机次数   管理员(上这几次机的每个管理员都列出来)

               秦云             3             李大伟,马化腾,李大伟

               在路上           2            马化腾,李大伟

               LEO              0       

如果不算管理员那一列的话,我是这样写的。

SELECT  表1.NAME AS 姓名, COUNT(表2.ID) AS 上机次数

FROM  表1 LEFT OUTER JOIN

      表2 ON 表1.NAME = 表2.NAME

GROUP BY 表1.名称

 

解答:
测试用例

 

create table 表1( --drop 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   ,’在路上’  ,’10378’        ,’13600000’)

insert into 表1 values(3   ,’LEO’     ,’10000’        ,’13900000’)

 

create table 表2( --drop 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

 

 

测试结果:

 

姓名    上机次数    管理员

--------------------------------------------------------------

秦云        3       李大伟,马化腾,李大伟

在路上      2       马化腾,李大伟


 



分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间
点击: