SqlAlchemy:过滤以匹配所有而不是列表中的任何值?

2021-12-30 00:00:00 python sqlalchemy sql mysql

我想查询一个联结表中 aID 列的值，该值匹配 ids=[3,5] 列中的 id 列表的所有值>bID.

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 列中的 code>3 或 5.我希望查询返回的是 2 因为这是唯一一个 aID 值在其 中包含 ids 列表的所有值bID 列.

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?

推荐答案

您正在寻找适用于行集的查询.我认为使用带有子句的 group by 是最好的方法:

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)

相关文章