SQL WHERE IN子句中如何有多个列?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (1580)

我需要在SQLServer中实现以下查询:

select *
from table1
WHERE  (CM_PLAN_ID,Individual_ID)
IN
(
 Select CM_PLAN_ID, Individual_ID
 From CRM_VCM_CURRENT_LEAD_STATUS
 Where Lead_Key = :_Lead_Key
)

但是WHER.IN子句只允许1列。如何将两个或更多列与另一个内部选择进行比较?

提问于
用户回答回答于

你可以从子查询创建派生表,并将表1连接到此派生表:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL
用户回答回答于

你需要使用WHERSITS语法来代替。

SELECT *
FROM table1
WHERE EXISTS (SELECT *
              FROM table2
              WHERE Lead_Key = @Lead_Key
                        AND table1.CM_PLAN_ID = table2.CM_PLAN_ID
                        AND table1.Individual_ID = table2.Individual_ID)

扫码关注云+社区

领取腾讯云代金券