MySQL从2个不同表的一行中选择多行 [英] MySQL Select multiple rows from one row in 2 different tables

问题描述

我有这种情况:

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 中进行精确搜索"

I need to make an "exact search" in A using the B characteristics fields

例如

(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 个或更少的时间内完成此操作，然后查询每个特征和一个比较！?

How can i do this in 1 or less then one query each characteristic and a comparaction!?

谢谢！

推荐答案

您可以使用聚合和 sharing 子句来做到这一点:

You can do this with aggregation and a having clause:

select AID
from table B
group by AID
having sum(BID = 1 AND BV = 1) > 0 and
       sum(BID = 2 AND BV = 1) > 0;

have 子句中的每个条件计算满足其中一个条件的行数.您的逻辑要求两者都存在，因此行数需要大于 0.

Each condition in the having clause counts the number of rows that meet one of the conditions. Your logic requires that both be present, so the number of rows needs to be bigger than 0.

这篇关于MySQL从2个不同表的一行中选择多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！