27,579
社区成员
发帖
与我相关
我的任务
分享
-- 测试环境
if object_id('tb') is not null drop table tb
go
create table tb
(
id int,
name varchar(20)
)
go
insert tb
select 1,'A' union all
select 2,'B'
go
-- 查询
declare @s varchar(8000)
select @s=isnull(@s+' union all ','')+' select '''+name+''' as [db], sum(value) as value from '+name+'..test '
from tb
set @s = 'select * from ('+@s+') t'
print @s
--select * from ( select 'A' as [db], sum(value) as value from A..test union all select 'B' as [db], sum(value) as value from B..test ) t
exec (@s)
-- 测试环境
if object_id('tb') is not null drop table tb
go
create table tb
(
id int,
name varchar(20)
)
go
insert tb
select 1,'A' union all
select 2,'B'
go
-- 查询
declare @s varchar(8000)
select @s=isnull(@s+' union all ','')+' select value from '+name+'..test '
from tb
set @s = 'select sum(value) from ('+@s+') t'
--print @s
--select sum(value) from ( select value from A..test union all select value from B..test ) t
exec (@s)
DECLARE @s VARCHAR(8000)
SELECT @s = ISNULL(@s+' union all ','')+'select value from '+QUOTENAME(dbname)+'.dbo.test'
FROM a.dbo.dblist
WHERE dbname in('A','B','C'...)
EXEC ('select sum(value) from ('+@s+') t')
declare @var varchar(200),@TB_NAME VARCHAR(50),@COL_NAME VARCHAR(50)
SELECT @TB_NAME='TEST',@COL_NAME='VALUE'
select @var='selcet '+replace((SELECT '(SELECT sum('+@COL_NAME+') from '+DBNAME+'.dbo.'+@TB_NAME+')' as 'data()'
FROM #DB
WHERE DBNAME IN('A','B','C')--筛选数据库
for xml path('')),') (',')+(')+' as V_SUM'
print(@var)
--exec(@var)
create table #DB (DBNAME VARCHAR(20))
INSERT #DB
SELECT 'A' union all
SELECT 'B' union all
SELECT 'C' union all
SELECT 'D' union all
SELECT 'E'
declare @var varchar(200),@TB_NAME VARCHAR(50),@COL_NAME VARCHAR(50)
SELECT @TB_NAME='TEST',@COL_NAME='VALUE'
select @var='selcet '+replace((SELECT '(SELECT sum('+@COL_NAME+') from '+DBNAME+'.dbo.'+@TB_NAME+')' as 'data()'
FROM #DB
for xml path('')),') (',')+(')+' as V_SUM'
print(@var)
--exec(@var)
/*
selcet (SELECT sum(VALUE) from A.dbo.TEST)
+(SELECT sum(VALUE) from B.dbo.TEST)
+(SELECT sum(VALUE) from C.dbo.TEST)
+(SELECT sum(VALUE) from D.dbo.TEST)
+(SELECT sum(VALUE) from E.dbo.TEST) as V_SUM
*/
select sum(value)
from (
select value from a.dbo.test
union all
select value from b.dbo.test
.....
union all
select value from f.dbo.test
) as t
create table #DB (DBNAME VARCHAR(20))
INSERT #DB
SELECT 'A' union all
SELECT 'B' union all
SELECT 'C' union all
SELECT 'D' union all
SELECT 'E'
declare @var varchar(200)
select @var='selcet '+replace((SELECT '(SELECT sum(value) from '+DBNAME+'.dbo.TEST)' as 'data()' FROM #DB for xml path('')),') (',')+(')+' as V_SUM'
exec(@var)
DECLARE @SQL VARCHAR(1000)
set @SQL = ''
SELECT @SQL = isnull(@SQL+'SELECT SUM(VAlUE) VAlUE FROM ','') + quotename(name) + '.dbo.[test] UNION ALL 'FROM master.dbo.sysdatabases where name in ('A','B','C','D')
PRINT @SQL
SELECT @SQL = 'SELECT SUM(VAlUE) VAlUE FROM ('+LEFT(@SQL,LEN(@SQL)-LEN('UNION ALL '))+' ) AS T'
PRINT @SQL
EXEC (@SQL)