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|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|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|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.

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|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|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|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.

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

我需要在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 =...

问SQL WHERE..IN子句多列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE..IN子句多列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE..IN子句多列
EN