使用WHERE和多行变量组合SELECT结果

Question

我有一个用来返回数据的查询：

Select top 100 
       cnb.ndc_id, 
       cnb.contract_num_val, 
       cnb.quote_price, 
       cnb.eff_dt, 
       cnb.end_dt, 
       cnb.discount_pct, 
       cnb.rebate_pct
from   [NDC_ATTR] ndc 
  INNER JOIN CONTRACT_NDC_BRG cnb ON ndc.attr_val=cnb.NDC_ID
where ndc.field_id = 69 
and   cnb.contract_ndc_brg_id = @CONT;

当我运行这段代码时，我得到一个错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我知道这是因为@CONT有多行。但这正是我需要的。

我一直在看帖子，似乎不鼓励在SQL中使用循环。(如果我错了，请纠正我)

我如何返回上述查询的联合，但@CONT中的每一行都返回每一行？

编辑：

@CONT已定义：

declare @CONT varchar(50) = (   
   SELECT contract_ndc_brg_id
   FROM   contract_ndc_brg
     INNER JOIN ndc ON contract_ndc_brg.ndc_id = ndc.item_pk
   where item_id = @VALUE 
   );

Answer 1

如果您可以在查询中连接到@CONT，则可以摆脱ndc，如下所示：

select top 100 
   cnb.ndc_id
 , cnb.contract_num_val
 , cnb.quote_price
 , cnb.eff_dt
 , cnb.end_dt
 , cnb.discount_pct
 , cnb.rebate_pct
from [NDC_ATTR] ndc
  inner join CONTRACT_NDC_BRG cnb
    on ndc.attr_val = cnb.NDC_ID
  inner join ndc as i
      on cnb.ndc_id = i.item_pk
where ndc.field_id = 69 
   and i.item_id = @VALUE

或者转换为使用exists()，如下所示：

select top 100 
   cnb.ndc_id
 , cnb.contract_num_val
 , cnb.quote_price
 , cnb.eff_dt
 , cnb.end_dt
 , cnb.discount_pct
 , cnb.rebate_pct
from [NDC_ATTR] ndc
  inner join CONTRACT_NDC_BRG cnb
    on ndc.attr_val = cnb.NDC_ID
where ndc.field_id = 69 
  and exists (
    select 1
    from ndc as i
    where i.item_pk = cnb.ndc_id
      and i.item_id = @VALUE
    )

Answer 2

编辑:我刚刚在@cont后面看到了你的查询，这应该是可行的，但我个人更喜欢SqlZim给出的答案：

declare @CONT TABLE (value varchar(500)) 

SELECT contract_ndc_brg_id
FROM contract_ndc_brg
INNER JOIN ndc
ON contract_ndc_brg.ndc_id = ndc.item_pk
where item_id = @VALUE 

Select top 100 cnb.ndc_id, cnb.contract_num_val, cnb.quote_price,cnb.eff_dt, cnb.end_dt, cnb.discount_pct, cnb.rebate_pct
from [NDC_ATTR] ndc 
INNER JOIN CONTRACT_NDC_BRG cnb
ON ndc.attr_val=cnb.NDC_ID
where ndc.field_id=69 and cnb.contract_ndc_brg_id IN ( @CONT );

Answer 3

为什么要使用变量呢？只需插入子查询：

Select top 100 cnb.ndc_id, cnb.contract_num_val, cnb.quote_price, cnb.eff_dt, cnb.end_dt, cnb.discount_pct, cnb.rebate_pct
from [NDC_ATTR] ndc inner join
     CONTRACT_NDC_BRG cnb
     on ndc.attr_val=cnb.NDC_ID
where ndc.field_id = 69 and
      cnb.contract_ndc_brg_id in (select contract_ndc_brg_id
                                  from contract_ndc_brg join
                                       ndc
                                       on contract_ndc_brg.ndc_id = ndc.item_pk
                                  where item_id = @VALUE
                                 );