确定是否存在多对多记录组合 [英] Determine if many-to-many record combination exists

问题描述

使用简单的解决方案似乎将是一项常见任务，但我在StackOverflow和Google上都是空手而归.

This seems like it would be a common task with an easy solution but I've come up empty handed both on StackOverflow and Google.

方案是这样的:我有两个表A&拥有多对多关系的B.因此，我有带有外键的表A_B，该表映射了A到B的记录关系.标准的东西.

Scenario is this: I have two tables A & B that share a many-to-many relationship. As such I have table A_B with foreign keys which maps the A-to-B record relationships. Standard stuff.

我要弄清楚的是，如果已经存在匹配的相同关系，则在输入新记录(一个"A"记录与一个或多个"B"记录)之前如何查询表.目标是不重复数据.

All I'm trying to figure out is how to query the tables before I enter a new record (one 'A' record with one or more 'B' records) if a matching, identical relationship already exists. The goal is to not duplicate the data.

最后，这些表将变得非常大，因此我需要尽可能降低开销.

Finally, these tables will grow quite large so I need to keep overhead down where at all possible.

更新

下面是我正在尝试的示例查询，以确定是否存在映射到B_id值3、4和5的现有A记录.它可以工作，但是如果有两个跨越3的不同A_id值，则返回假阳性. ，4和5，例如:

Below is an example query I was trying, to determine if there was an existing A record mapped to B_id values 3, 4, and 5. It works, however it returns false positives if there are two different A_id values that span 3, 4, and 5, e.g.:

• A_id = 1，B_id值= 2、3
• A_id = 2，B_id值= 4、5、6

SELECT A_id, B_id
FROM A_B
GROUP BY
A_id HAVING
    B_id IN (3,4,5)
    AND
    COUNT(*) = 3
LIMIT 1

更新2

A_B表的主键是包含A_id和B_id的复合键.

A_B表定义了一个唯一的组合键，包括A_id和B_id.

The A_B table defines a unique composite key including A_id and B_id.

单个A由一个或多个B组成.

A single A is comprised of one or more Bs.

表达这个问题的一种更通用的方法:给定一组有限的B id值，我需要确定是否存在一个由该精确的B组组成的现有A.如果不是，则使用该组B创建一个新的A.

A more general way to phrase this question: given a finite set of B id values, I need to be able to determine if there is an existing A comprised of that exact set of Bs. If not, a new A is created with that set of Bs.

欢呼

推荐答案

GROUP_CONCAT进行救援！给定B_id值1、3和5，这就是您(I)如何确定该唯一组合是否存在并同时检索对应的A_id:

GROUP_CONCAT to the rescue! Given B_id values 1, 3 and 5, here's how you (I) can determine if that unique combination exists and retrieve the corresponding A_id at the same time:

SELECT A_id FROM A_B
GROUP BY A_id
HAVING GROUP_CONCAT(B_id) = '1,3,5';

不需要子查询，可以通过在A_B表上添加复合索引A_id_B_id进行优化.

No subqueries required, and may be optimized by adding a composite index A_id_B_id on the A_B table.

感谢参加者最终引导我朝着正确的方向前进.

Thanks to participants who chimed in and ultimately steered me in the right direction.

欢呼

这篇关于确定是否存在多对多记录组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！