34,590
社区成员
发帖
与我相关
我的任务
分享
--建表
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为登陆密码
-->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
*/