SqlAlchemy:筛选以匹配所有而不是列表中的任何值? [英] SqlAlchemy: filter to match all instead of any values in list?

问题描述

我想在联结表中查询与列bID中的ID ids=[3,5]列表的所有值匹配的列aID的值.

I want to query a junction table for the value of column aID that matches all values of a list of ids ids=[3,5] in column bID.

这是我的联结表(JT):

 aID    bID
   1      1
   1      2
   2      5
   2      3
   1      3
   3      5

我有以下查询:session.query(JT.aID).filter(JT.bID.in_(ids)).all()

此查询返回aID值1，2和3，因为它们都在bID列中包含带有3或5的行.我希望查询返回的是2，因为这是唯一的aID值，在其bID列中具有ids列表的所有值.

This query returns the aID values 1, 2 and 3 because they all have rows with either 3 or 5 in the bID column. What I want the query to return is 2 because that is the only aID value that has all values of the ids list in its bID column.

不知道如何更好地解释问题，但是如何得到结果呢?

Don't know how to explain the problem better, but how can I get to the result?

推荐答案

您正在寻找对行集有效的查询.我认为一个具有having子句的小组是最好的方法:

You are looking for a query that works on sets of rows. I think a group by with having clause is the best approach:

select aid
from jt
where bid in (<your list>)
group by aid
having count(distinct bid) = 2

如果您可以将所需的ID放在表中，则可以执行以下更通用的方法:

If you can put the ids that you desire in a table, you can do the following more generic approach:

select aid
from jt join
     bids
     on jf.bid = bids.bid
group by aid
having count(distinct jt.bid) = (select count(*) from bids)

这篇关于SqlAlchemy:筛选以匹配所有而不是列表中的任何值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！