declare @str varchar(4000)
set @str=''
select @str=@str+','+a from #a where .....--你的条件
select @str=substring(@str,2,4000)exec('select '+@str+' from #b')
set @str=''
select @str=@str+','+a from #a where .....--你的条件
select @str=substring(@str,2,4000)exec('select '+@str+' from #b')
set @s=''
select @s=@s+',['+a+']' from #a
set @s=substring(@s,2,8000)
exec('select '+@s+' from #b')
如
create function fun_test(@condition varchar(300))
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+','+a from #a where .....=@condition --你的条件
select @str=substring(@str,2,4000)
return @str
end然后外面就可以直接调用了
exec('select '+dbo.fun_test(@condition)+' from #b')
如
create function fun_test(@condition varchar(300))
returns varchar(4000)
as
begin
declare @str varchar(4000)
set @str=''
select @str=@str+','+a from #a where .....=@condition --你的条件
select @str=substring(@str,2,4000)
return @str
end然后外面就可以直接调用了
exec('select '+dbo.fun_test(@condition)+' from #b')
create table #a(a varchar(20))
insert #a select 'a1'
union all select 'a2'create table #b(a1 INT,a2 INT)
insert #b select 100,200
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+a+']' from #a
set @s=substring(@s,2,8000)
exec('select '+@s+' from #b')
go--删除测试
drop table #a,#b/*--测试结果
a1 a2
----------- -----------
100 200--*/
declare @sql varchar(256)
set @allfield=''
select @allfield=@allfield+fields+',' from #a
set @sql='select '+substring(@allfield,1,len(@allfield)-1)+' from #b
Execute(@sql)
可以说,你们的方法都可以实现以上的功能,但本人碰到的问题好象要复杂些,以下是源码:SELECT a.* FROM B_BASeData_New a,B_Rule b,B_SubRule c,B_ChgFactor d
WHERE a.TypeID = b.TypeID AND a.PayID = b.PayID
AND (c.is_fixed*fixfee + c.is_phonefee*a.Total_Charge*c.feepercent/100) <= b.FstAmount
AND b.payid =c.payid AND b.typeid = c.typeid
AND c.factorid = d.factorid AND d.value_policy = 1
AND a.'+ d.factorfield +' between c.startvalue AND c.endvalue(说明:d.factorfield 字段的内容为对应a的字段名)
现在的问题就在“a.'+ d.factorfield +'”这里(出错--不知道该怎么写?)
假如使用游标遍历,完全可以把“d.factorfield”赋给一个变量,再执行以上的语句,但因为数据量有好几万,所以执行起来特别慢最好的办法就可以在以上的语句里实现类似“a.'+ d.factorfield +'”的写法,不知是否可行?
WHERE a.TypeID = b.TypeID AND a.PayID = b.PayID
AND (c.is_fixed*fixfee + c.is_phonefee*a.Total_Charge*c.feepercent/100) <= b.FstAmount
AND b.payid =c.payid AND b.typeid = c.typeid
AND c.factorid = d.factorid AND d.value_policy = 1
AND a.[d.factorfield] between c.startvalue AND c.endvalue问题的关键在于怎样实现a.[d.factorfield] 这个字段的表示方法;
当然也可以使用其它方法来实现,但效率一定要保证