连接表中的查询要求 [英] Query Req in Junction Table

问题描述

附表，

offerauto  statusauto
1               2
2               2
2               3
2               10
3               2
3               7
3               10
4               2
4              10
5              10
---------

我想获取状态为自动2&的相同offerauto的列表. 10必须
不是2或10的记录，都必须
例如:我需要offerauto = 3，其中有2& 10
没有其他提供具有2或10或为null或为null的offerauto

I want to fetch the list of same offerauto which have status auto 2 & 10 must
not the record which have 2 or 10 ,both are must
for:eg I need offerauto=3 which have 2 & 10
not others offerauto which have 2 or 10 or null

推荐答案

尝试此

select  offerauto
from    myTable
where   statusauto in(2,10)
group
by      offerauto
having  count(distinct statusauto)=2

如果我正确理解了这个问题，则可以使用自我连接，例如:

If I understood the question correctly, you could use self join, for example:

SELECT *
FROM TableName a, 
     TableName b
WHERE a.OfferAuto  = b.OfferAuto
AND   a.StatusAuto = 2
AND   b.StatusAuto = 10

offerauto statusauto
1 2
2 2
2 3
2 10
3 2
3 7
3 10
4 2
4 10
5 10

---------

offerauto statusauto
1 2
2 2
2 3
2 10
3 2
3 7
3 10
4 2
4 10
5 10

---------

这篇关于连接表中的查询要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！