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

 

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

假设环境如下：

    表 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       马化腾 , 李大伟