连接中的嵌套选择：无法绑定多部分标识符

Question

这是一个令人讨厌的。我试图从嵌套在联接中的内部选择引用外部查询中的表。我需要根据外部 sales_contract 表中的值过滤这些内部查询，但当我尝试在内部查询中引用这些列中的任何列时，我收到“无法绑定多部分标识符”错误：

sc.commission_range_start
sc.commission_range_end
sc.commission_duration
sc.signup_range_start
sc.signup_range_end

如何重构此查询？

with account_hierarchy(account_major, account_minor) as
(
    select
        gp.account_major as account_major,
        gp.account_major as account_minor
    from
        group_accounts gp with(nolock)
        left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor
    where
        gc.account_major is null
    group by
        gp.account_major

    union all

    select 
        up.account_major, down.account_minor
    from 
        group_accounts as down with(nolock)
        join account_hierarchy as up on up.account_minor = down.account_major
)
select
    sa.first_name as 'AgentFirstName',
    sa.last_name as 'AgentLastName',
    sc.threshold as 'CommissionUsageThreshold',
    sc.commission_amount as 'CommissionAmount',
    sc.commission_percentage as 'CommissionPercentage',
    parent.primary_phone as 'AccountNumber',
    child.primary_phone as 'ChildAccountNumber',
    coalesce(total_credit.amount, 0) as 'CreditTotal',
    coalesce(total_billing.amount, 0) as 'BillingTotal'
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    inner join
    (
        select
            d.primary_phone
        from
            demographic d
        where
            (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and
            (d.active_date < sc.signup_range_end or sc.signup_range_end is null)
    ) commission on
        child.primary_phone = commission.primary_phone
    left outer join
    (
        select
            j.primary_phone, sum(j.billed_amount) as amount
        from
            jobs_complete j
        where
            j.time_proofed >= sc.commission_range_start
            and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start))
            and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null)
        group by
            j.primary_phone
    ) total_billing on
        commission.primary_phone = total_billing.primary_phone
    left outer join
    (
        select
            c.primary_phone, sum(c.amount) as amount
        from
            credit c
        group by
            c.primary_phone
    ) total_credit on
        commission.primary_phone = total_credit.primary_phone
where
    sd.email = @DistributorEmail

Answer 1

我认为您需要更改引用 CROSS APPLY 和 OUTER APPLY 外部表的 JOIN。试试这个：

with account_hierarchy(account_major, account_minor) as
(
    select
        gp.account_major as account_major,
        gp.account_major as account_minor
    from
        group_accounts gp with(nolock)
        left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor
    where
        gc.account_major is null
    group by
        gp.account_major

    union all

    select 
        up.account_major, down.account_minor
    from 
        group_accounts as down with(nolock)
        join account_hierarchy as up on up.account_minor = down.account_major
)
select
    sa.first_name as 'AgentFirstName',
    sa.last_name as 'AgentLastName',
    sc.threshold as 'CommissionUsageThreshold',
    sc.commission_amount as 'CommissionAmount',
    sc.commission_percentage as 'CommissionPercentage',
    parent.primary_phone as 'AccountNumber',
    child.primary_phone as 'ChildAccountNumber',
    coalesce(total_credit.amount, 0) as 'CreditTotal',
    coalesce(total_billing.amount, 0) as 'BillingTotal'
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    cross apply
    (
        select
            d.primary_phone
        from
            demographic d
        where
            (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and
            (d.active_date < sc.signup_range_end or sc.signup_range_end is null) and
            d.primary_phone = child.primary_phone
    ) commission 
    outer apply
    (
        select
            j.primary_phone, sum(j.billed_amount) as amount
        from
            jobs_complete j
        where
            j.time_proofed >= sc.commission_range_start
            and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start))
            and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null)
            and j.primary_phone = child.primary_phone
        group by
            j.primary_phone
    ) total_billing
    left outer join
    (
        select
            c.primary_phone, sum(c.amount) as amount
        from
            credit c
        group by
            c.primary_phone
    ) total_credit on
        commission.primary_phone = total_credit.primary_phone
where
    sd.email = @DistributorEmail

Answer 2

您可以尝试将子查询移动到视图中，然后加入视图。

select ...
from
    sales_contract sc
    inner join sales_agent sa on sc.sales_agent_id = sa.id
    inner join sales_distributor sd on sa.distributor_id = sd.id
    inner join demographic parent on sc.primary_phone = parent.primary_phone
    inner join account_hierarchy ah on parent.primary_phone = ah.account_major
    inner join demographic child on ah.account_minor = child.primary_phone
    inner join vwCommission on child.primary_phone = vwCommission.primary_phone
        and ...
    left outer join vwTotal_Billing on commission.primary_phone = vwTotal_Billing.primary_phone
        and ...
    left outer join vwTotal_credit on commission.primary_phone = vwTotal_credit.primary_phone
        and ...
where ...