如何基于另一表字段中的数据库名称实现跨库查询？

这种多租户分库场景下，静态SQL无法直接引用字段中的数据库名称，必须通过动态SQL实现运行时拼接数据库名的逻辑，以下是两种常见场景的具体实现：

1. 单条记录查询（对应你示例中WHERE id = xxxx的场景） #

先从relations表获取目标数据库名称，再拼接动态SQL执行：

DECLARE @TargetID INT = xxxx; -- 替换为实际要查询的ID
DECLARE @DBName NVARCHAR(128);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 获取对应租户的数据库名
SELECT @DBName = [database] FROM relations WHERE id = @TargetID;

-- 拼接带动态数据库名的SQL，注意用QUOTENAME处理数据库名避免语法错误和注入
SET @DynamicSQL = N'
SELECT 
    r.id, 
    r.[database], 
    p.warehouse, 
    p.image 
FROM relations r
LEFT JOIN ' + QUOTENAME(@DBName) + N'.dbo.products p
    ON r.record_id = p.id -- 此处需根据实际业务调整关联字段
WHERE r.id = @TargetID';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL, N'@TargetID INT', @TargetID;

2. 批量查询多条记录的场景 #

如果需要一次性处理多条relations记录，可以通过拼接UNION ALL或者游标循环实现：

方式一：拼接UNION ALL批量查询 #

DECLARE @DynamicSQL NVARCHAR(MAX);

-- 自动拼接所有租户库的查询语句并合并结果
SET @DynamicSQL = STUFF((
    SELECT N'
    UNION ALL
    SELECT 
        r.id, 
        r.[database], 
        p.warehouse, 
        p.image 
    FROM relations r
    LEFT JOIN ' + QUOTENAME(r.[database]) + N'.dbo.products p
        ON r.record_id = p.id -- 调整实际关联字段
    WHERE r.[database] = ''' + r.[database] + ''''
    FROM (SELECT DISTINCT [database] FROM relations) r
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 11, N'');

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL;

方式二：游标循环+临时表存储结果 #

适合需要对每个租户数据做单独处理的场景：

-- 创建临时表存储待处理的relations记录
CREATE TABLE #TempRelations (id INT, [database] NVARCHAR(128), record_id INT);
INSERT INTO #TempRelations 
SELECT id, [database], record_id FROM relations WHERE id IN (xxxx, yyyy); -- 替换为目标ID集合

-- 创建结果表存储最终数据
CREATE TABLE #FinalResults (id INT, [database] NVARCHAR(128), warehouse NVARCHAR(255), image NVARCHAR(255));

DECLARE @DBName NVARCHAR(128);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 游标遍历所有需要处理的租户数据库
DECLARE db_cursor CURSOR FOR
SELECT DISTINCT [database] FROM #TempRelations;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DynamicSQL = N'
    INSERT INTO #FinalResults
    SELECT 
        tr.id, 
        tr.[database], 
        p.warehouse, 
        p.image 
    FROM #TempRelations tr
    LEFT JOIN ' + QUOTENAME(@DBName) + N'.dbo.products p
        ON tr.record_id = p.id -- 调整实际关联字段
    WHERE tr.[database] = @DBName';

    EXEC sp_executesql @DynamicSQL, N'@DBName NVARCHAR(128)', @DBName;

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- 查询最终结果
SELECT * FROM #FinalResults;

-- 清理临时表
DROP TABLE #TempRelations;
DROP TABLE #FinalResults;

注意事项 #

• 确保执行SQL的账号拥有所有租户数据库的访问权限，否则会出现权限不足错误。
• 始终用QUOTENAME()包裹数据库名，避免因数据库名包含特殊字符（如空格、连字符）导致语法错误，同时防范SQL注入风险。
• JOIN的关联条件需根据实际业务逻辑调整，原示例中缺少这部分，这是LEFT JOIN必须的语法要素。

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