MySQL:选择字段与另一个表的所有相同字段匹配的外键 [英] MySQL: Selecting foreign keys with fields matching all the same fields of another table

问题描述

从我读到的内容来看，这是一种应该用除法运算符完成的事情，但显然在 MySQL 中没有实现.基本上，我的设置是一个包含两列的表，其中键多次列出，第二列中的列表不同，例如

From what I'm reading, this is the sort of thing that is meant to be done with a division operator, but apparently that is not implemented in MySQL. Basically, my setup is a table with two columns where keys are listed multiple times with different listings in the second column, e.g.

PID | GID
A1  | G1
A1  | G2
A2  | G1
A2  | G3
A3  | G1
A3  | G2
A4  | G2
A4  | G3

基本上，我必须根据 PID 和与其关联的所有 GID 找到表的子集，例如A3分配的所有GID，比较容易得到，生成一个中间表

Basically I have to find a subset of the table based on PID and all the GIDs associated with it, e.g. all GIDs that A3 is assigned, which is fairly easy to get, producing a intermediate table

PID | GID
A3  | G1
A3  | G2

但是给我带来麻烦的是弄清楚如何在原始表中选择与子集表的所有 GID 匹配的记录.正如我所说，我能找到的一切都指向除法运算符，但这在 MySQL 中不存在，所以我有点不知所措.我想不出一种加入或执行匹配所有 GID 的操作的方法——只有部分匹配，这不是我要找的.有什么帮助吗?我正在看的书不是特别有用.

But what's giving me trouble is figuring out how to select the records in the original table that match ALL of the GIDs for the subset table. As I said, everything I've been able to find points towards a division operator, but that doesn't exist in MySQL, so I'm kind of at the end of my rope. I can't figure out a way to join or do an operation that matches all GIDs - only partial matches, which isn't what I'm looking for. Any help? The book I'm looking at isn't particularly helpful.

推荐答案

所以您有一个 GID 列表，并且您想要列表中包含所有 GID 的所有 PID?

So you have a list of GIDs and you want all the PIDs that have all the GIDs in the list?

鉴于(例如在您的程序语言中)您可以确定有多少个 GID，一个相当奇怪但可行的方法是:

Given that (e.g. in your procedural language) you can determine how many GIDs you have, a rather strange but feasible way is this:

SELECT FROM the_table WHERE GID IN ('G1','G2') GROUP BY PID HAVING COUNT(*) = 2

这篇关于MySQL:选择字段与另一个表的所有相同字段匹配的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！