我需要在SQL Server中实现以下查询:
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
)
但是WHERE..IN子句只允许1列。如何将2个或更多列与另一个内部选择进行比较?
发布于 2009-07-16 07:48:09
您可以从子查询创建一个派生表,并将table1联接到此派生表:
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
发布于 2009-07-16 07:46:42
您将希望改用WHERE EXISTS语法。
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)
发布于 2009-07-16 08:15:29
简单的EXISTS子句是最干净的
select *
from table1 t1
WHERE
EXISTS
(
Select * --or 1. No difference...
From CRM_VCM_CURRENT_LEAD_STATUS Ex
Where Lead_Key = :_Lead_Key
-- correlation here...
AND
t1.CM_PLAN_ID = Ex.CM_PLAN_ID AND t1.CM_PLAN_ID = Ex.Individual_ID
)
如果在关联中有多个行,那么连接将在输出中提供多个行,因此需要distinct。这通常会使EXISTS更有效率。
注意:带有连接的SELECT *
还将包括行限制表中的列
https://stackoverflow.com/questions/1136380
复制相似问题