时间戳

首页 > 解决方案 > 子查询中的动态数据库

sql - 子查询中的动态数据库

问题描述

SELECT
 email, password,GP_employee_id, company,                                                
 (select distinct CHEKNMBR from [BSL].[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20 ) as slip_number,
 (select distinct CONVERT(date , CHEKDATE) from [BSL].[dbo].[UPR30300] WHERE EMPLOYID = GP_employee_id and CHEKDATE > GETDATE() - 20 ) as slip_number
FROM [payslips].[dbo].[myapp_user]

我希望 [BSL] 是动态的。该值将取决于主查询的公司字段。所以我想要这样的 [company].[dbo].[UPR30300]

标签： sqlsql-servertsql

解决方案

你可以做一个大的动态UNION ALL查询

DECLARE @unioned nvarchar(max) = (
    SELECT STRING_AGG(CAST(
'
    SELECT *, company = ' + QUOTENAME(company, '''')  + '
    FROM ' + QUOTENAME(company) + '.[dbo].[UPR30300]
    WHERE CHEKDATE > DATEADD(day, -20, GETDATE())
'
      AS nvarchar(max)), 'UNION ALL')
    FROM (
        SELECT DISTINCT company
        FROM [payslips].[dbo].[myapp_user]
    ) au
);

DECLARE @sql nvarchar(max) = '
SELECT
    au.email,
    au.password,
    au.GP_employee_id,
    au.company,                                                
    slip_number = u.CHEKNMBR,
    slip_number2 = CONVERT(date, u.CHEKDATE)
FROM [payslips].[dbo].[myapp_user] au
LEFT JOIN (
' + @unioned + '
) u ON u.company = au.company
    AND u.EMPLOYID = au.GP_employee_id;
';

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;

推荐阅读