SQL SERVER 2008中如何跨越多个数据库取数,整合到一张新视图里面?
发布网友
发布时间：2022-04-09 17:48
共1个回答
热心网友
时间：2022-04-09 19:18
本机数据库间的跨度，引用举例：
联合查询这两个表
select * from dbA.dbo.tb1 t1
left join dbB.dbo.tb2 t2 on t1.key1=t2.key2
关键：表明的前缀：dbA.dbo.tb1，即：数据库名.dbo.表名
计算机网络间数据库跨度，举例：
SELECT I.*, M.*FROM OPENROWSET('SQLOLEDB', '192.168.10.2'; 'sa'; '123', [Database1].[dbo].[Table1]) I
INNER JOIN (
SELECT DISTINCT P.IPSAppID AppId, P.DepartmentId, C.CategoryId, T.Creator
FROM OPENROWSET
('SQLOLEDB', '192.168.10.1'; 'sa'; '123', [Database2].[dbo].[table2]) P LEFT JOIN (
SELECT DISTINCT LTRIM(DepartmentId) DepartmentId, UserName Creator
FROM OPENROWSET('SQLOLEDB', '192.168.10.1'; 'sa'; '123', [Database3].[dbo].[view1])
WHERE UserName IN
(SELECT DISTINCT Creator
FROM OPENROWSET('SQLOLEDB', '192.168.10.1'; 'sa'; '123', [Database1].[dbo].[Table1]) WHERE AppId = '8cefca00-9733-4976-951a-e19346603717' AND CategoryId = 'b14b4852-c0e4-4db6-a91e-704b699a8fa2')) T
ON P.DepartmentId = T.DepartmentId
LEFT JOIN OPENROWSET('SQLOLEDB', '192.168.10.1'; 'sa'; '123', [Database2].[dbo].[Table3]) C ON C.AppId = P.IPSAppID
WHERE P.ClassId IS NULL AND T.Creator IS NOT NULL AND C.CategoryName = 'activity') M
ON M.Creator = I.Creator
WHERE I.AppId = '8cefca00-9733-4976-951a-e19346603717' AND I.CategoryId = 'b14b4852-c0e4-4db6-a91e-704b699a8fa2'
