日期:2014-05-17  浏览次数:20759 次

怎么在SQL语句中动态处理表名?
表tb中有一列为type,char(1),
比如type可能为A,语句想完成的功能为select tb.*,(select count(*) from type_A where ...) as 数量 ,怎么将type_A替换为动态的类似type_ + tb.type

------解决方案--------------------
这个需要动态拼接SQL语句

exec('动态拼接的语句')
------解决方案--------------------
SQL code

-->try
create table tb(f1 int,type varchar(1))
insert into tb
select 1,'A' union all
select 2,'B' union all
select 3,'C'
create table type_A(col1 int)
insert into type_A
select 1 union all
select 2
create table type_B(col1 int)
insert into type_B
select 1 union all
select 2
create table type_C(col1 int)
insert into type_C
select 1 union all
select 2
go
declare @sql varchar(4000)
select @sql=isnull(@sql+' union all ','')+'select count(*) as cnt,'''+type+''' as type from type_'+type
from tb
set @sql='select tb.*,t.cnt from tb,('+@sql+')t where tb.type=t.type'
--print @sql
exec(@sql)
/*
f1          type cnt
----------- ---- -----------
1           A    2
2           B    2
3           C    2
*/