表tb中有一列为type,char(1),
比如type可能为A,语句想完成的功能为select tb.*,(select count(*) from type_A where ...) as 数量 ,怎么将type_A替换为动态的类似type_ + tb.type
比如type可能为A,语句想完成的功能为select tb.*,(select count(*) from type_A where ...) as 数量 ,怎么将type_A替换为动态的类似type_ + tb.type
-->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
*/
http://blog.csdn.net/htl258/article/details/5659935
--建表
create table tb(f1 int,type varchar(1))
insert into tb
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
create table type_A(col1 int)
insert into type_A
select 1 union all
select 2
go
create table type_B(col1 int)
insert into type_B
select 1 union all
select 2
go
create table type_C(col1 int)
insert into type_C
select 1 union all
select 2
go
--建sp
create proc procTest
as
begin
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)
end
--建view
create view viewTest
as
select * from openrowset('sqloledb','Server';'user';'pwd','set fmtonly off exec dbo.ProcTest')
--其中Server为你的数据库实例或填IP及端口
--user为登陆用户名
--pwd为登陆密码