假如有这样一对主从表 ,表1表示基本信息,表2表示扩展信息,以采购为例,主表表示采购员基本信息,为简化,我们字段名全部用中文,分两列,一是序号,二是姓名,另外一个是采购明细,这里只列出物品名称字段,两个表如下:
1、采购员信息
序号 | 姓名 |
1 | 张三 |
2 | 李四 |
2、采购记录
序号 | 采购员序号 | 物品名称 |
1 | 1 | 打印纸 |
2 | 1 | 电脑 |
3 | 2 | 衣服 |
4 | 2 | 手机 |
“序号”字段为int 型,为主键,因此不能重复,在表2中,采购员序号字段同表1中的序号是对应的,现在我们要查询采购员信息,并顺便将他采购的内容显示到后面,每个采购内容间用“,”分开,也就是得到一张视图,如下所示:
序号 | 姓名 | 物品汇总 |
1 | 张三 | 打印纸,电脑 |
2 | 李四 | 衣服,手机 |
因为是要得到一个视图,用储存过程显示无法做到,只有用函数了,当然,你也可以在客户端根据查询结果一条一条查询采购的物品,但要多次往返于C/S端,影响速度,创建视图无疑是最好的办法,但视图中只能用函数不能用储存过程,而函数又不支持游标,该如何处理呢?在工作中,我利用下面的办法来模拟了一个“游标”,从而达到效果(如果在SQL Server 2005中用C#写扩展内存是比较容易的,这里不讨论这个问题):
create FUNCTION [dbo].[fGetGoodNamesByOpId](
@OpId int --采购员序号
)
RETURNS nvarchar (2000)--查询结果,用“,”分开
AS
begin
declare @a nvarchar(2000)
set @a=''
declare @CurId int
declare @CurName nvarchar(20)
set @CurId=-1
set @CurName=''
while (@CurName is not null)
begin
set @CurName =null
--关键是下面这句话,每次查询一条记录,且按序号排序,实际上是通过序号进行循环
select top 1 @CurId=序号,@CurName=物品名称 from 采购信息 where 采购员序号=@OpId
And 序号>@CurId order by 序号
if @CurName is not null
begin
if len(@a)>0
set @a=@a+','
set @a=@a+@CurName
end
end
return @a
end
有了上面的函数,就可以用下面的查询语句创建视图了
select *,dbo.fGetGoodNamesByOpId(序号) from 采购员信息
上面的方法虽然比较笨,速度也不快,但的确能解决问题,希望对你有所帮助。