sql - 如何在变量中加入动态sql语句和普通语句

Question

我有一个非常复杂的查询，它将动态构建并保存在一个变量中。

作为第二部分，我有另一个普通查询，我想在这两者之间建立一个内部连接。

为了让它更容易一点，这里有一个小例子来说明我的问题。
对于这个小例子，我使用了 AdventureWorks database .

一些动态构建的查询

(是的，我知道这里没有动态，因为它只是一个例子。)

DECLARE @query AS varchar(max) ;

set @query = '
select
    HumanResources.Employee.EmployeeID
    ,HumanResources.Employee.LoginID
    ,HumanResources.Employee.Title
    ,HumanResources.EmployeeAddress.AddressID
from
    HumanResources.Employee
    inner join HumanResources.EmployeeAddress
    on HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
;';

EXEC (@query);

我的普通查询

select
    Person.Address.AddressID
    ,Person.Address.City
from
    Person.Address

也许是我想要但行不通的东西

select
    @query.*
    ,Addresses.City
from
    @query as Employees
    inner join
    (
        select
            Person.Address.AddressID
            ,Person.Address.City
        from
            Person.Address
    ) as Addresses
    on Employees.AddressID = Addresses.AddressID

Answer 1

使用临时表并将记录转储到其中(来自动态查询)并使用临时表与您拥有的静态查询连接。

set @query = 'CREATE table #myTempTable AS
select
    HumanResources.Employee.EmployeeID
    ,HumanResources.Employee.LoginID
    ,HumanResources.Employee.Title
    ,HumanResources.EmployeeAddress.AddressID
from
    HumanResources.Employee
    inner join HumanResources.EmployeeAddress
    on HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
;';

EXEC (@query);

然后

select
    Employees.*
    ,Addresses.City
from
    #myTempTable as Employees
    inner join
    (
        select
            Person.Address.AddressID
            ,Person.Address.City
        from
            Person.Address
    ) as Addresses
    on Employees.AddressID = Addresses.AddressID