如何在OPENQUERY链接服务器的WHERE子句中关联本地SQL Server表？

我正在学习SQL Server的链接服务器，已成功将Oracle数据库链接到SQL Server，目前能执行以下查询：

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients')

我在SQL Server本地有一张PatientTable表，想把这张表的ID作为上述OPENQUERY查询的WHERE子句过滤条件，但不知道怎么实现。我期望的效果类似以下两种写法：

第一种写法示例：

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients')
where "--OPENQUERY返回的ID字段" IN (Select ID from PatientTable)

第二种写法示例：

SELECT *
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients where ID in (--本地PatientTable的ID集合)')

更新：我测试了Stu提供的解决方案，接近成功，但无法在外部WHERE子句中调用OPENQUERY返回的列字段，错误信息如下：

消息 207，级别 16，状态 1，第X行：无效的列名 'ID'

解决方案 #

方法1：外部关联本地表（推荐，避免SQL注入）

通过JOIN或EXISTS关联本地表，注意Oracle返回的列名可能区分大小写，需确保与本地表列名匹配：

-- 使用INNER JOIN过滤
SELECT o.*
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients') o
INNER JOIN PatientTable p 
    ON o.ID = p.ID

-- 使用WHERE EXISTS过滤（适合大表，性能更优）
SELECT o.*
FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients') o
WHERE EXISTS (
    SELECT 1 
    FROM PatientTable p 
    WHERE p.ID = o.ID
)

如果遇到列名识别错误（比如Oracle返回的列是小写id），可以在OPENQUERY中给列加别名统一命名：

SELECT o.*
FROM OPENQUERY(DB_ORCL,'select Name, "ID" as ID from OdataLink.patients') o
WHERE o.ID IN (SELECT ID FROM PatientTable)

方法2：动态拼接SQL（将本地ID集合传入Oracle查询）

通过动态SQL把本地表的ID集合拼接进OPENQUERY的Oracle语句中，注意SQL注入风险：

-- 拼接本地表ID（SQL Server 2017及以上用STRING_AGG）
DECLARE @Ids VARCHAR(MAX)
SELECT @Ids = STRING_AGG(CAST(ID AS VARCHAR), ',') FROM PatientTable

-- 低版本SQL Server用STUFF+FOR XML PATH拼接
-- SELECT @Ids = STUFF((SELECT ',' + CAST(ID AS VARCHAR) FROM PatientTable FOR XML PATH('')), 1, 1, '')

-- 构造动态SQL并执行
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'SELECT * FROM OPENQUERY(DB_ORCL, ''select Name, ID from OdataLink.patients where ID in (' + @Ids + ')'' )'
EXEC sp_executesql @Sql

注意事项 #

• 方法1需确保链接服务器配置支持双向访问，且列名大小写与Oracle返回的一致
• 方法2若ID为字符串类型，需额外处理引号转义（比如REPLACE(ID, '''', '''''')），避免SQL注入
• 若仍出现列名错误，可先单独执行SELECT * FROM OPENQUERY(DB_ORCL,'select Name, ID from OdataLink.patients')，查看结果集中的实际列名

内容的提问来源于stack exchange，提问作者Christiano