表a结构:
ACCOUNT_NO; //VARCHAR(20),
VOUCHER_NO;//VARCHAR(50),单据号
YESNO;//VARCHAR(1),a表的数据如下:
ACCOUNT_NO VOUCHER_NO YESNO10101 F000001 1
10202 F000001 1
204 F000001 0
205 F000001 0
304 F000002 0
10203 F000002 1
204 F000002 0
10203 F000003 1
401 F000003 0
203 F000004 0
204 F000004 0………………………………
现要得到如下视图:
ACCOUNT_NO VOUCHER_NO YESNO OTHER_ACCOUNT10101 F000001 1 10202,204,205
10202 F000001 1 10101,204,205
204 F000001 0
205 F000001 0
304 F000002 0
10203 F000002 1 304,204
204 F000002 0
10203 F000003 1 401
401 F000003 0
203 F000004 0
204 F000004 0
…………………………
ACCOUNT_NO; //VARCHAR(20),
VOUCHER_NO;//VARCHAR(50),单据号
YESNO;//VARCHAR(1),a表的数据如下:
ACCOUNT_NO VOUCHER_NO YESNO10101 F000001 1
10202 F000001 1
204 F000001 0
205 F000001 0
304 F000002 0
10203 F000002 1
204 F000002 0
10203 F000003 1
401 F000003 0
203 F000004 0
204 F000004 0………………………………
现要得到如下视图:
ACCOUNT_NO VOUCHER_NO YESNO OTHER_ACCOUNT10101 F000001 1 10202,204,205
10202 F000001 1 10101,204,205
204 F000001 0
205 F000001 0
304 F000002 0
10203 F000002 1 304,204
204 F000002 0
10203 F000003 1 401
401 F000003 0
203 F000004 0
204 F000004 0
…………………………
create table tb2 (id int,type nvarchar(10))
insert into tb2 select 1,'a'
insert into tb2 select 1,'b'
insert into tb2 select 1,'c'
insert into tb2 select 1,'d'
insert into tb2 select 2,'a'
insert into tb2 select 3,'b'
insert into tb2 select 4,'c'
insert into tb2 select 4,'d'
go
--查询处理
create function f_catString(@id int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
set @s=''
select @s=@s+','+type from tb2 where id=@id
return(stuff(@s,1,1,''))
end
go
--调用函数
select id,dbo.f_catString(id) as type from tb2 group by id
go--删除测试环境
Drop Table tb2
Create table a
(ACCOUNT_NO VARCHAR(20),
VOUCHER_NO VARCHAR(50),
YESNO VARCHAR(1)
)
GO
--插入数据
Insert a Values('10101', 'F000001', 1)
Insert a Values('10202', 'F000001', 1)
Insert a Values('204', 'F000001', 0)
Insert a Values('205', 'F000001', 0)
Insert a Values('304', 'F000002', 0)
Insert a Values('10203', 'F000002', 1)
Insert a Values('204', 'F000002', 0)
Insert a Values('10203', 'F000003', 1)
Insert a Values('401', 'F000003', 0)
Insert a Values('203', 'F000004', 0)
Insert a Values('204', 'F000004', 0)
--建立函数
GO
Create Function GetACCOUNT_NO(@ACCOUNT_NO Nvarchar(100),@VOUCHER_NO Nvarchar(100),@YESNO Nvarchar(1))
Returns Nvarchar(200)
As
Begin
declare @s Nvarchar(4000)
Set @s=''
If @YESNO='1'
Select @s=@s+','+ACCOUNT_NO from a where VOUCHER_NO=@VOUCHER_NO And ACCOUNT_NO<>@ACCOUNT_NO And YESNO=0
Set @s=Stuff(@s,1,1,'')
Return (@s)
End
GO
--建立视图
Create View [GetOTHER_ACCOUNT]
As
Select ACCOUNT_NO,VOUCHER_NO,YESNO,dbo.GetACCOUNT_NO(ACCOUNT_NO,VOUCHER_NO,YESNO) As OTHER_ACCOUNT From a
GO
--测试
Select * from GetOTHER_ACCOUNT
--删除测试环境
Drop table a
Drop View [GetOTHER_ACCOUNT]
Drop Function GetACCOUNT_NO
--结果
/*
ACCOUNT_NO VOUCHER_NO YESNO OTHER_ACCOUNT
10101 F000001 1 204,205
10202 F000001 1 204,205
204 F000001 0 NULL
205 F000001 0 NULL
304 F000002 0 NULL
10203 F000002 1 304,204
204 F000002 0 NULL
10203 F000003 1 401
401 F000003 0 NULL
203 F000004 0 NULL
204 F000004 0 NULL
*/
Create table a
(ACCOUNT_NO VARCHAR(20),
VOUCHER_NO VARCHAR(50),
YESNO VARCHAR(1)
)
GO
--插入数据
Insert a Values('10101', 'F000001', 1)
Insert a Values('10202', 'F000001', 1)
Insert a Values('204', 'F000001', 0)
Insert a Values('205', 'F000001', 0)
Insert a Values('304', 'F000002', 0)
Insert a Values('10203', 'F000002', 1)
Insert a Values('204', 'F000002', 0)
Insert a Values('10203', 'F000003', 1)
Insert a Values('401', 'F000003', 0)
Insert a Values('203', 'F000004', 0)
Insert a Values('204', 'F000004', 0)
--建立函数
GO
Create Function GetACCOUNT_NO(@ACCOUNT_NO Nvarchar(100),@VOUCHER_NO Nvarchar(100),@YESNO Nvarchar(1))
Returns Nvarchar(200)
As
Begin
declare @s Nvarchar(4000)
Set @s=''
If @YESNO='1'
Select @s=@s+','+ACCOUNT_NO from a where VOUCHER_NO=@VOUCHER_NO And ACCOUNT_NO<>@ACCOUNT_NO
Set @s=Stuff(@s,1,1,'')
Return (@s)
End
GO
--建立视图
Create View [GetOTHER_ACCOUNT]
As
Select ACCOUNT_NO,VOUCHER_NO,YESNO,dbo.GetACCOUNT_NO(ACCOUNT_NO,VOUCHER_NO,YESNO) As OTHER_ACCOUNT From a
GO
--测试
Select * from GetOTHER_ACCOUNT
--删除测试环境
Drop table a
Drop View [GetOTHER_ACCOUNT]
Drop Function GetACCOUNT_NO
--结果
/*
ACCOUNT_NO VOUCHER_NO YESNO OTHER_ACCOUNT
10101 F000001 1 10202,204,205
10202 F000001 1 10101,204,205
204 F000001 0 NULL
205 F000001 0 NULL
304 F000002 0 NULL
10203 F000002 1 304,204
204 F000002 0 NULL
10203 F000003 1 401
401 F000003 0 NULL
203 F000004 0 NULL
204 F000004 0 NULL
*/