跨服务器、数据库、表联合查询

最新推荐文章于 2023-09-08 09:17:28 发布

原创最新推荐文章于 2023-09-08 09:17:28 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #服务器 #database #table #join #null

Database 专栏收录该内容

61 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询涉及多个数据库表的连接操作，包括内连接、左连接等，通过逐步筛选条件实现精确的数据提取。适用于需要从多个数据源中整合信息并进行深入分析的场景。

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'