blocks|key|1503204|text|您可以从子查询创建一个派生表，并将table1联接到此派生表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503205|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|code-block|syntax|javascript|1503206|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can make a derived table from the subquery, and join table1 to this derived table:

<pre><code>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
</code></pre>

blocks|key|1288333|text|您将希望改用WHERE+EXISTS语法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288334|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)|code-block|syntax|javascript|1288335|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You'll want to use the WHERE EXISTS syntax instead.

<pre><code>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)
</code></pre>

blocks|key|1288448|text|简单的EXISTS子句是最干净的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288449|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
)|code-block|syntax|javascript|1288450|如果在关联中有多个行，那么连接将在输出中提供多个行，因此需要distinct。这通常会使EXISTS更有效率。|1288451|注意:带有连接的SELECT+*还将包括行限制表中的列|offset|length|style|CODE|1288452|entityMap^0|0|0|0|8|8|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

A simple EXISTS clause is cleanest

<pre><code>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
)
</code></pre>

If you have multiple rows in the correlation then a JOIN gives multiple rows in the output, so you'd need distinct. Which usually makes the EXISTS more efficient.

Note <code>SELECT *</code> with a JOIN would also include columns from the row limiting tables

blocks|key|1503312|text|为什么要使用WHERE+EXISTS或派生表，而您只能执行普通的内连接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503313|SELECT+t.*
FROM+table1+t
INNER+JOIN+CRM_VCM_CURRENT_LEAD_STATUS+s
++++ON+t.CM_PLAN_ID+=+s.CM_PLAN_ID
++++AND+t.Individual_ID+=+s.Individual_ID
WHERE+s.Lead_Key+=+:_Lead_Key|code-block|syntax|javascript|1503314|如果(CM_PLAN_ID，Individual_ID)对在状态表中不是唯一的，那么您可能需要一个SELECT+DISTINCT+t.*。|1503315|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Why use WHERE EXISTS or DERIVED TABLES when you can just do a normal inner join:

<pre><code>SELECT t.*
FROM table1 t
INNER JOIN CRM_VCM_CURRENT_LEAD_STATUS s
 ON t.CM_PLAN_ID = s.CM_PLAN_ID
 AND t.Individual_ID = s.Individual_ID
WHERE s.Lead_Key = :_Lead_Key
</code></pre>

If the pair of (CM_PLAN_ID, Individual_ID) isn't unique in the status table, you might need a SELECT DISTINCT t.* instead.

blocks|key|1288851|text|Postgres+SQL++:+version+9.6
Total+records+on+tables+:+mjr_agent+=+145,+mjr_transaction_item+=+91800|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1288852|1.使用EXISTS平均查询时间:+1.42s|unstyled|offset|length|style|CODE|1288853|SELECT+count(txi.id)+
FROM+
mjr_transaction_item+txi
WHERE+
EXISTS+(+SELECT+1+FROM+mjr_agent+agnt+WHERE+agnt.agent_group+=+0+AND+(txi.src_id+=+agnt.code+OR+txi.dest_id+=+agnt.code)+)+|1288854|2.使用两行IN子句平均查询时间:+0.37s|1288855|SELECT+count(txi.id)+FROM+mjr_transaction_item+txi
WHERE+
txi.src_id+IN+(+SELECT+agnt.code+FROM+mjr_agent+agnt+WHERE+agnt.agent_group+=+0+)+
OR+txi.dest_id+IN+(+SELECT+agnt.code+FROM+mjr_agent+agnt+WHERE+agnt.agent_group+=+0+)|1288856|3.使用INNNER+JOIN模式平均查询时间:+2.9s|1288857|SELECT+count(DISTINCT(txi.id))+FROM+mjr_transaction_item+txi
INNER+JOIN+mjr_agent+agnt+ON+agnt.code+=+txi.src_id+OR+agnt.code+=+txi.dest_id
WHERE+
agnt.agent_group+=+0|1288858|因此，我选择了第二种选择。|1288859|entityMap^0|0|4|6|0|0|6|2|0|0|4|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|Z|8|@$G|10|H|11|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|13|8|@$G|14|H|15|I|J]]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$B|C]]|$1|Q|3|R|5|F|7|17|8|@$G|18|H|19|I|J]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$B|C]]|$1|U|3|V|5|F|7|1B|8|@]|9|@]|A|$]]|$1|W|3|-4|5|F|7|1C|8|@]|9|@]|A|$]]]|X|$]]

<pre><code>Postgres SQL : version 9.6
Total records on tables : mjr_agent = 145, mjr_transaction_item = 91800
</code></pre>

1.Using with <code>EXISTS</code> [Average Query Time : 1.42s]

<pre><code>SELECT count(txi.id) 
FROM 
mjr_transaction_item txi
WHERE 
EXISTS ( SELECT 1 FROM mjr_agent agnt WHERE agnt.agent_group = 0 AND (txi.src_id = agnt.code OR txi.dest_id = agnt.code) ) 
</code></pre>

2.Using with two lines <code>IN</code> Clause [Average Query Time : 0.37s]

<pre><code>SELECT count(txi.id) FROM mjr_transaction_item txi
WHERE 
txi.src_id IN ( SELECT agnt.code FROM mjr_agent agnt WHERE agnt.agent_group = 0 ) 
OR txi.dest_id IN ( SELECT agnt.code FROM mjr_agent agnt WHERE agnt.agent_group = 0 )
</code></pre>

3.Using with <code>INNNER JOIN</code> pattern [Average Query Time : 2.9s]

<pre><code>SELECT count(DISTINCT(txi.id)) FROM mjr_transaction_item txi
INNER JOIN mjr_agent agnt ON agnt.code = txi.src_id OR agnt.code = txi.dest_id
WHERE 
agnt.agent_group = 0
</code></pre>

So , I choosed second option.

blocks|key|1503480|text|查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503481|select+ord_num,+agent_code,+ord_date,+ord_amount
from+orders
where+(agent_code,+ord_amount)+IN
(SELECT+agent_code,+MIN(ord_amount)
FROM+orders+
GROUP+BY+agent_code);|code-block|syntax|javascript|1503482|上面的查询在mysql中对我有效。请参阅以下链接-->|1503483|https://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php|offset|length|1503484|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|25|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|W|8|@]|9|@$K|X|L|Y|1|Z]]|A|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|J]]]]

Query:

<pre><code>select ord_num, agent_code, ord_date, ord_amount
from orders
where (agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders 
GROUP BY agent_code);
</code></pre>

above query worked for me in mysql. 
refer following link -->

<a href="https://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php" rel="nofollow noreferrer">https://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php</a>

blocks|key|1288651|text|如果需要一个表，则使用以下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288652|SELECT+S.*+
FROM+Student_info+S
++INNER+JOIN+Student_info+UT
++++ON+S.id+=+UT.id
++++AND+S.studentName+=+UT.studentName
where+S.id+in+(1,2)+and+S.studentName+in+('a','b')|code-block|syntax|javascript|1288653|表数据，如下所示|1288654|id%7Cname%7Cadde%7Ccity
1+++a+++ad++ca
2+++b+++bd++bd
3+++a+++ad++ad
4+++b+++bd++bd
5+++c+++cd++cd|1288655|然后输出如下|1288656|id%7Cname%7Cadde%7Ccity
1+++a+++ad++ca
2+++b+++bd++bd|1288657|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

If you want for one table then use following query

<pre><code>SELECT S.* 
FROM Student_info S
 INNER JOIN Student_info UT
 ON S.id = UT.id
 AND S.studentName = UT.studentName
where S.id in (1,2) and S.studentName in ('a','b')
</code></pre>

and table data as follow

<pre><code>id|name|adde|city
1 a ad ca
2 b bd bd
3 a ad ad
4 b bd bd
5 c cd cd
</code></pre>

Then output as follow

<pre><code>id|name|adde|city
1 a ad ca
2 b bd bd
</code></pre>

blocks|key|1503446|text|我们可以简单地这样做。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503447|+++select+*
+++from+
++++table1+t,+CRM_VCM_CURRENT_LEAD_STATUS+c
++++WHERE++t.CM_PLAN_ID+=+c.CRM_VCM_CURRENT_LEAD_STATUS
++++and+t.Individual_ID+=+c.Individual_ID|code-block|syntax|javascript|1503448|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

We can simply do this. 

<pre><code> select *
 from 
 table1 t, CRM_VCM_CURRENT_LEAD_STATUS c
 WHERE t.CM_PLAN_ID = c.CRM_VCM_CURRENT_LEAD_STATUS
 and t.Individual_ID = c.Individual_ID
</code></pre>

blocks|key|1288783|text|以某种形式将列连接在一起是一种"hack"，但当产品不支持多个列的半连接时，有时您别无选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288784|内部/外部连接解决方案不起作用的示例：|1288785|select+*+from+T1+
+where+<boolean+expression>
+++and+(<boolean+expression>+OR+(ColA,+ColB)+in+(select+A,+B+...))
+++and+<boolean+expression>
+++...|code-block|syntax|javascript|1288786|当查询本质上不是微不足道的时候，有时您无法访问设置为执行常规内/外连接的基表。|1288787|如果你确实使用了这种"hack"，当你组合字段时，只需确保在它们之间添加足够的分隔符，以避免误解，例如ColA+%2B+":-:"+%2B+ColB|offset|length|style|CODE|1288788|entityMap^0|0|0|0|0|1F|J|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Concatenating the columns together in some form is a "hack", but when the product doesn't support semi-joins for more than one column, sometimes you have no choice.

Example of where inner/outer join solution would not work:

<pre><code>select * from T1 
 where &lt;boolean expression&gt;
 and (&lt;boolean expression&gt; OR (ColA, ColB) in (select A, B ...))
 and &lt;boolean expression&gt;
 ...
</code></pre>

When the queries aren't trivial in nature sometimes you don't have access to the base table set to perform regular inner/outer joins.

If you do use this "hack", when you combine fields just be sure to add enough of a delimiter in between them to avoid misinterpretations, e.g. <code>ColA + ":-:" + ColB</code>

blocks|key|1503349|text|我发现这样更容易|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1503350|Select+*+
from+table1+
WHERE++(convert(VARCHAR,CM_PLAN_ID)+%2B+convert(VARCHAR,Individual_ID))+
IN+
(
+Select+convert(VARCHAR,CM_PLAN_ID)+%2B+convert(VARCHAR,Individual_ID)
+From+CRM_VCM_CURRENT_LEAD_STATUS+
+Where+Lead_Key+=+:_Lead_Key+
)+|code-block|syntax|javascript|1503351|希望这能对您有所帮助:)|1503352|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I founded easier this way

<pre><code>Select * 
from table1 
WHERE (convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)) 
IN 
(
 Select convert(VARCHAR,CM_PLAN_ID) + convert(VARCHAR,Individual_ID)
 From CRM_VCM_CURRENT_LEAD_STATUS 
 Where Lead_Key = :_Lead_Key 
) 
</code></pre>

Hope this help :)

blocks|key|1288616|text|简单而错误的方法是使用%2B或连接来组合两列，并生成一列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288617|Select+*
from+XX
where+col1%2Bcol2+in+(Select+col1%2Bcol2+from+YY)|code-block|syntax|javascript|1288618|这将是相当缓慢的。不能在编程中使用，但如果您只是查询以验证某些内容，则可以使用。|1288619|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Simple and wrong way would be combine two columns using + or concatenate and make one columns.

<pre><code>Select *
from XX
where col1+col2 in (Select col1+col2 from YY)
</code></pre>

This would be offcourse pretty slow. Can not be used in programming but if in case you are just querying for verifying something may be used.

I need to implement the following query in SQL Server:

<pre><code>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
)
</code></pre>

But the WHERE..IN clause allows only 1 column. How can I compare 2 or more columns with another inner SELECT?

SQL WHERE.. IN clause multiple columns

我需要在SQL Server中实现以下查询：select *from table1WHERE  (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个或更多列

问SQL WHERE..IN子句多列
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE..IN子句多列EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE..IN子句多列
EN