如何获取where子句的all of value表条件结果
如何获取where子句的all of value表条件结果
提问于 2016-09-08 15:20:42
我有三个表A和B和C，A的主键在B中，C的主键在B中：
表A：
id
---
1
2
3
4表B：
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表C：
id name
---------
1
2
3
4我有一个疑问，它有一个where子句，由列代码B的值和A的返回记录组成
我想要得到A的记录，这些记录有where子句的全部代码，而不是其中的一部分。
例如：
SELECT ID FROM A a INNER JOIN B b
ON a.id = b.A_id
where b.code in(20, 30, 40);我的期望值仅低于A的结果：
id
----
1因为顶部结果(“1”)仅具有代码(20，30，40)所有值，且另一个示例可以是：
SELECT ID FROM A a INNER JOIN B b
ON a.id = b.A_id
where b.code in(30, 40);我的期望是：
id
----
4回答 4
Stack Overflow用户
发布于 2016-09-08 16:29:28
我将使用listagg将所有代码连接成一个字符串(agregated_codes)，然后检查是否匹配(最后一条select语句)。但在此之前，我将只从Bdata (distinct_data)收集不同的记录
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'))结果：
for '20,30,40'结果为1
for '30,40'结果为4
Stack Overflow用户
发布于 2016-09-08 15:27:38
您可以像这样尝试：
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在这里，最后一行count(A.Id) = 3将确保在满足IN子句中指定的所有代码时返回Id。
编辑：
试试这个：
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) ;Stack Overflow用户
发布于 2016-09-08 15:40:33
使用exists子句。
仅选择代码值为20、30和40的记录。
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);仅选择代码值为30和40的记录。
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);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39384805
复制相关文章
相似问题
腾讯云开发者
