MySQL 从 2 个不同表中的一行中选择多行

Question

我有这种情况:

Table A (Ex. Dog's name):
ID | Name
1  | Nabu
2  | Lapo
3  | Kim
3  | Bau

Table B (Ex. Dog's characteristics):
ID | AID | BID | BV
1  |  1  |  1  |  1
2  |  1  |  2  |  1
3  |  1  |  3  |  0
4  |  2  |  1  |  1
5  |  2  |  2  |  0
6  |  2  |  3  |  1
7  |  3  |  1  |  0
8  |  3  |  2  |  1
9  |  3  |  3  |  1
10 |  4  |  1  |  1
11 |  4  |  2  |  1
12 |  4  |  3  |  1

我需要使用 B 特征字段在 A 中进行“精确搜索”

例如

(BID = 1 && BV = 1) AND (BID = 2 && BV = 1)  have to return A.ID = 1 & 4
(BID = 1 && BV = 0) AND (BID = 2 && BV = 1)  have to return A.ID = 3

我怎样才能在 1 个或更少的查询每个特征和一个比较中做到这一点!？

谢谢!

Answer 1

如果您自己加入表格:

SELECT A.id FROM A 
LEFT JOIN B as B1 on A.ID = B1.AID
LEFT JOIN B as B2 on A.ID = B2.AID and B1.ID != B2.ID
WHERE
(B1.BID = 1 && B1.BV = 1) AND (B2.BID = 2 && B2.BV = 1)

您应该能够找到 A 中的所有行，其中 B.ID 不同的两个匹配条目。