MySQL：选择字段与另一个表的所有相同字段匹配的外键

发布于 2024-09-30 23:36:44 字数 496 浏览 1 评论 0原文

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

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

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

PID | GID
A3  | G1
A3  | G2

但给我带来麻烦的是弄清楚如何选择原始表中与子集表的所有 GID 匹配的记录。正如我所说，我能找到的所有内容都指向除法运算符，但 MySQL 中不存在除法运算符，所以我有点束手无策。我无法找到一种方法来加入或执行匹配所有 GID 的操作 - 仅部分匹配，这不是我正在寻找的。有什么帮助吗？我正在看的书并不是特别有帮助。

原文

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

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞花火° 2024-10-07 23:36:44

那么您有一个 GID 列表，并且您想要包含列表中所有 GID 的所有 PID？

鉴于（例如，在您的过程语言中）您可以确定您拥有多少个 GID，一个相当奇怪但可行的方法是：

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

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

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

回复收藏 0 原文

羁客 2024-10-07 23:36:44

我不是 100% 确定这就是您所追求的，因为您的问题有点令人困惑，但我们会看到...

您似乎在执行子查询之后：

SELECT pid, gid FROM your_table WHERE gid in (SELECT gid FROM your_table WHERE pid = 'A3')

这将从表中选择 GID 所在的所有行匹配与 PID“A3”关联的任何 GID。你可能需要慢慢地再读一遍……太拗口了。

I'm not 100% sure this is what you're after since your question is a bit confusing, but we'll see…

You appear to be after a subquery:

SELECT pid, gid FROM your_table WHERE gid in (SELECT gid FROM your_table WHERE pid = 'A3')

That'll select all of the rows from your table where the GID matches any of the GIDs that are associated with the PID 'A3'. You might need to read that over again slowly…it's a mouthful.

回复收藏 0 原文

~没有更多了~