blocks|key|4213743|text|您可以像这样尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4213744|Select+A.Id
from+A+a+INNER+JOIN+B+b
++++++++ON+a.id+=+b.A_id
where+b.code+in(20,+30,+40)
group+by+A.Id
having+count(A.Id)+=+3|code-block|syntax|javascript|4213745|在这里，最后一行count(A.Id)+=+3将确保在满足IN子句中指定的所有代码时返回Id。|offset|length|style|CODE|4213746|编辑：|BOLD|4213747|试试这个：|4213748|SELECT+DISTINCT+(A.Id)+
FROM+A+a+INNER+JOIN+B+b
++++++++++++ON+a.id+=+b.A_id
WHERE+A.Id+In+(select+A_id+from+user+where+code+=+20)+
++++And+A.Id+In+(select+A_id+from+user+where+code+=+30)++
++++And+A.Id+In+(select+A_id+from+user+where+code+=+40)+;|4213749|entityMap^0|0|0|8|F|T|2|0|0|3|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|W|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|X|8|@$I|Y|J|Z|K|L]|$I|10|J|11|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|12|8|@$I|13|J|14|K|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|T|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|U|$]]

You can try like this:

<pre><code>Select A.Id
from A a INNER JOIN B b
 ON a.id = b.A_id
where b.code in(20, 30, 40)
group by A.Id
having count(A.Id) = 3
</code></pre>

Here the last line <code>count(A.Id) = 3</code> will make sure that it will return Id when it meets all the code specified in the <code>IN</code> clause.

EDIT:

Try this:

<pre><code>SELECT DISTINCT (A.Id) 
FROM A a INNER JOIN B b
 ON a.id = b.A_id
WHERE A.Id In (select A_id from user where code = 20) 
 And A.Id In (select A_id from user where code = 30) 
 And A.Id In (select A_id from user where code = 40) ;
</code></pre>

blocks|key|5682137|text|使用exists子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5682138|仅选择代码值为20、30和40的记录。|5682139|select+distinct+a_id+from+B+outB
where+exists+(select+code+from+B++where+outB.a_id+=+a_id+and+code+=20)
and+exists+(select+*+from+B+where+outB.a_id+=+a_id+and+code+=30)
and+exists+(select+*+from+B+where+outB.a_id+=+a_id+and+code+=40);|code-block|syntax|javascript|5682140|仅选择代码值为30和40的记录。|5682141|select+distinct+a_id+from+B+outB
where+not+exists+(select+*+from+B+where+outB.a_id+=+a_id+and+code+=20)
and+exists+(select+*+from+B+where+outB.a_id+=+a_id+and+code+=30)
and+exists+(select+*+from+B+where+outB.a_id+=+a_id+and+code+=40);|5682142|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Use exists clause.

To select only those records that have code values 20,30 and 40.

<pre><code>select distinct a_id from B outB
where exists (select code from B where outB.a_id = a_id and code =20)
and exists (select * from B where outB.a_id = a_id and code =30)
and exists (select * from B where outB.a_id = a_id and code =40);
</code></pre>

To select only those records that have code values 30 and 40.

<pre><code>select distinct a_id from B outB
where not exists (select * from B where outB.a_id = a_id and code =20)
and exists (select * from B where outB.a_id = a_id and code =30)
and exists (select * from B where outB.a_id = a_id and code =40);
</code></pre>

blocks|key|4213791|text|我将使用listagg将所有代码连接成一个字符串(agregated_codes)，然后检查是否匹配(最后一条select语句)。但在此之前，我将只从Bdata+(distinct_data)收集不同的记录|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4213792|with
Adata+as+(
select+1+id+from+dual+union+all
select+2+id+from+dual+union+all
select+3+id+from+dual+union+all
select+4+id+from+dual+
),
+Bdata+as+(
select+1+id,+++++1+a_id,++++++++20+code,++++++++1+c_id+from+dual+union+all
select+2+id,+++++1+a_id,++++++++30+code,++++++++1+c_id+from+dual+union+all
select+3+id,+++++1+a_id,++++++++40+code,++++++++3+c_id+from+dual+union+all
select+4+id,+++++2+a_id,++++++++20+code,++++++++2+c_id+from+dual+union+all
select+5+id,+++++3+a_id,++++++++30+code,++++++++3+c_id+from+dual+union+all
select+6+id,+++++4+a_id,++++++++40+code,++++++++2+c_id+from+dual+union+all
select+7+id,+++++4+a_id,++++++++30+code,++++++++2+c_id+from+dual+union+all
select+8+id,+++++1+a_id,++++++++20+code,++++++++3+c_id+from+dual+union+all
select+9+id,+++++4+a_id,++++++++30+code,++++++++4+c_id+from+dual
),
distinct_data+as
(
select+distinct+a_id,code+from+Bdata+
),
agregated_codes+as+
(
select+a_id,+listagg(code,',')+within+group+(order+by+a_id)+codes++++
from+distinct_data++group+by+a_id+
)
select+*+from+Adata+where+id+in+(+select+a_id+from+agregated_codes+where+codes+in+('20,30,40'))|code-block|syntax|javascript|4213793|结果：|BOLD|4213794|for+'20,30,40'结果为1|4213795|for+'30,40'结果为4|4213796|entityMap^0|4|7|P|F|23|5|2A|D|0|0|0|3|0|4|A|H|1|0|4|7|E|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@$9|14|A|15|B|M]]|D|@]|E|$]]|$1|N|3|O|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|S|$]]

I will use <code>listagg</code> to concat all codes into one string (<code>agregated_codes</code>) , and then check for matches (final select statement). But before that, I'll collect only distinct records from <code>Bdata</code> (<code>distinct_data</code>)

<pre><code>with
Adata as (
select 1 id from dual union all
select 2 id from dual union all
select 3 id from dual union all
select 4 id from dual 
),
 Bdata as (
select 1 id, 1 a_id, 20 code, 1 c_id from dual union all
select 2 id, 1 a_id, 30 code, 1 c_id from dual union all
select 3 id, 1 a_id, 40 code, 3 c_id from dual union all
select 4 id, 2 a_id, 20 code, 2 c_id from dual union all
select 5 id, 3 a_id, 30 code, 3 c_id from dual union all
select 6 id, 4 a_id, 40 code, 2 c_id from dual union all
select 7 id, 4 a_id, 30 code, 2 c_id from dual union all
select 8 id, 1 a_id, 20 code, 3 c_id from dual union all
select 9 id, 4 a_id, 30 code, 4 c_id from dual
),
distinct_data as
(
select distinct a_id,code from Bdata 
),
agregated_codes as 
(
select a_id, listagg(code,',') within group (order by a_id) codes 
from distinct_data group by a_id 
)
select * from Adata where id in ( select a_id from agregated_codes where codes in ('20,30,40'))
</code></pre>

Results:

for <code>'20,30,40'</code> result is <code>1</code>

for <code>'30,40'</code> result is <code>4</code>

blocks|key|4213825|text|您可以在单个列中收集每个B.code值的所有B.A_id值，然后将此列与条件字符串(如'10,20,30'+)进行比较。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4213826|SELECT+t.A_id+
++FROM+(+SELECT+t_B.A_id,+LISTAGG(t_B.code,+',')++WITHIN+GROUP+(ORDER+BY+t_B.code)+AS+all_codes+FROM+(SELECT+DISTINCT+B.A_id,+B.code+FROM+B)+t_B++GROUP+BY+t_B.A_id)+t
+WHERE+t.all_codes+LIKE+'30,40'|code-block|syntax|javascript|4213827|我已经更正了我的代码，DISTINCT补充道。|4213828|entityMap^0|C|6|M|6|17|A|0|0|B|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

You can collect all <code>B.code</code> values in single column for each <code>B.A_id</code> value, and then just compare this column with condition string like <code>'10,20,30'</code>.

<pre><code>SELECT t.A_id 
 FROM ( SELECT t_B.A_id, LISTAGG(t_B.code, ',') WITHIN GROUP (ORDER BY t_B.code) AS all_codes FROM (SELECT DISTINCT B.A_id, B.code FROM B) t_B GROUP BY t_B.A_id) t
 WHERE t.all_codes LIKE '30,40'
</code></pre>

I have corrected my code, added <code>DISTINCT</code>.

I have three table A and B and C that primary key of A exists in B and primary key of C exists in B: 
Table A:

<pre><code>id
---
1
2
3
4
</code></pre>

Table B:

<pre><code>id A_id code c_id 
-----------------------------
1 1 20 1
2 1 30 1
3 1 40 3
4 2 20 2
5 3 30 3
6 4 40 2
7 4 30 2
8 1 20 3
9 4 30 4
</code></pre>

Table C:

<pre><code> id name
 ---------
 1
 2
 3
 4
</code></pre>

I have a query that it has a where clause consist of value of column code of B and return records of A 
I want to get records of A that those have total code of where clause instead of some of it. for example:

<pre><code>SELECT ID FROM A a INNER JOIN B b
 ON a.id = b.A_id
 where b.code in(20, 30, 40);
</code></pre>

My expectancy is only below result of A: 

<pre><code>id
----
1
</code></pre>

Because that top result("1") only has all of value of code(20, 30, 40)
and another example ca be:

<pre><code>SELECT ID FROM A a INNER JOIN B b
 ON a.id = b.A_id
 where b.code in(30, 40);
</code></pre>

My expectancy is:

<pre><code>id
----
4
</code></pre>

how to get result of a table condition on having all of value of where clause

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有三个表A和B和C，A的主键在B中，C的主键在B中：表A：id---1234表B：id   A_id     code      c_id  -----------------------------1     1        20        12     1        30        13     1...

问如何获取where子句的all of value表条件结果
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取where子句的all of value表条件结果EN