获取 A 列中具有共同值和 B 列中具有特定值的所有行 [英] Getting all rows with common value in column A and specific value in column B

问题描述

在 MySQL 中，我有 matcher_table 表，其中包含 matcher 和 type 列.放在此处的记录对于 type 列可以有许多不同的值.仅当 matcher 列的值相同时，一种类型才匹配另一种类型.
假设我必须找到 5 个 类型 的所有 匹配 记录.为了实现这一目标，哪种方法/查询是最好的?

In MySQL I have table matcher_table with columns matcher and type. Records which are put here can have many different values for type column. One type matches another one only when value for matcher column is the same.
Let's say I have to find all matching records for 5 types. Which would be the best approach/query in order to achieve this?

表格将是:

CREATE TABLE `matcher_table` (
  `id` INT NOT NULL,
  `matcher` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

现在假设我们在表中有这些值:

Now let's say we have this values in the table:

id | matcher | type
1  | match1  | type1
2  | match1  | type2
3  | match1  | type3
4  | match2  | type4
5  | match2  | type2
6  | match3  | type1
7  | match3  | type2
8  | match3  | type3

如果我需要获取类型(type1、type2、type3)的匹配数据，那么我必须获取 ID 为 1、2、3、6、7、8 的行(由于 match1 和 match3).
如果我需要获取类型(类型 1、类型 2、类型 3、类型 4)的匹配数据，那么我必须没有获得满足此匹配的记录.

If I need to get matching data for types (type1, type2, type3), than I must get rows with ID 1, 2, 3, 6, 7, 8 (due to match1 and match3).
If I need to get matching data for types (type1, type2, type3, type4) than I must get no records fulfilling this match.

推荐答案

我推荐三个 exists 子句——因为你想要原始行:

I would recommend three exists clauses -- because you want the original rows:

select mt.*
from matcher_table mt
where exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type1'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type2'
             ) and
      exists (select 1
              from matcher_table mt2
              where mt2.matcher = mt.matcher and mt2.type = 'type3'
             );

这种方法的优点是它避免了聚合，并且可以利用 matcher_table(matcher, type) 上的索引.与其他方法相比，我希望它具有非常好的性能.

The advantage of this approach is that it avoids aggregation and it can make use of an index on matcher_table(matcher, type). I would expect this to have very good performance in comparison to other approaches.

这篇关于获取 A 列中具有共同值和 B 列中具有特定值的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！