create table T
(A int,B varchar(20),C varchar(20))
insert t
select 1,'E1','6KC' union all
select 1,'F2','CKA' union all
select 1,'H3','MM' union all
select 2,'44','T2' union all
select 2,'GH','578'
create Function T_Fun(@A int,@B varchar(800))
returns varchar(8000)
as
begin
declare @T_SQL varchar(8000)
set @T_SQL=''
select @T_SQL=@T_SQL + B +'-'+C +',' from T where B+C<>@B and A=@A
set @T_SQL=left(@T_SQL,len(@T_SQL)-1)
return @T_SQL
end
create function f_he(@a int,@b varchar(20),@c varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+b+'-'+c from t where A=@a and B<>@b and C<>@c
return(stuff(@sql,1,1,''))
end
应该改为:
create function f_he(@a int,@b varchar(20),@c varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+b+'-'+c from t where A=@a and (B<>@b or C<>@c)
return(stuff(@sql,1,1,''))
end
原因很简单:
A=@a and B<>@b and C<>@c
这个条件如果有B相同但C不同他也给去掉了。
如多插入一条1,"E1","AAA"
两个结果就会很明显。
create table t
(A int,B varchar(20),C varchar(20))
insert t
select 1,'E1','6KC' union all
select 1,'F2','CKA' union all
select 1,'H3','MM' union all
select 2,'44','T2' union all
select 2,'GH','578'
go
create function f_he(@a int,@b varchar(20),@c varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(50)
set @sql=''
select @sql=@sql+','+b+'-'+c from t where A=@a and B<>@b and C<>@c
return(stuff(@sql,1,1,''))
end
go
select *,dbo.f_he(A,B,C) as D from t
drop function f_he
drop table t
A B C D
----------- -------------------- -------------------- --------------------------------------------------
1 E1 6KC F2-CKA,H3-MM
1 F2 CKA E1-6KC,H3-MM
1 H3 MM E1-6KC,F2-CKA
2 44 T2 GH-578
2 GH 578 44-T2