我如何比较MySQL中相同字段的两个不同值?

I have 3 tables in MySQL representing Suppliers that sell Parts through a Catalog:

我在MySQL中有3个表，代表通过目录销售零件的供应商:

Suppliers(sid, sname, address), sid being the primary key (an integer)

供应商(sid, sname, address)， sid是主键(整数)

Parts(pid, pname, color), pid being the primary key (an integer)

部件(pid, pname，颜色)，pid作为主键(一个整数)

Catalog(sid, pid, cost), sid and pid being the primary key

目录(sid、pid、cost)、sid和pid是主键

Now I am trying to select the names of the Suppliers that are supplying two Parts of different colors. So far I have only managed to figure out how to select the sid's of suppliers that are selling more than one part with the following code:

现在我正在尝试选择供应两部分不同颜色的供应商的名字。到目前为止，我只找到了如何选择销售超过一个零件的供应商的sid's，代码如下:

SELECT sid
FROM catalog
GROUP BY sid
HAVING COUNT(sid) > 1;

I am however at a loss with how to select two parts from a specific supplier and compare their color values. It seems like I need some kind of subquery but I am not sure exactly how to go about it. Any guidance in the right direction would be much appreciated.

但是我不知道如何从一个特定的供应商中选择两个零件并比较它们的颜色值。似乎我需要某种子查询，但我不确定该怎么做。任何正确方向的指导都将受到赞赏。

1 个解决方案

#1

For this, you could use a self join where you declare that the PID must be the same but the Color must be different, similar to the below:

为此，您可以使用self join，其中您声明PID必须相同，但是颜色必须不同，类似于以下内容:

SELECT s.sid, s.sname, p.*
FROM (
    SELECT DISTINCT a.pid, a.color, b.color
    FROM parts AS a
    JOIN parts AS b ON b.pid == a.pid AND b.color <> a.color
) AS p
JOIN catalog AS c ON c.pid == p.pid
JOIN suppliers AS s ON s.sid == c.sid

No need for a having clause, even if you want to use a GROUP BY to accomplish the same thing:

即使你想通过使用GROUP BY来完成同样的事情，也不需要使用have子句:

SELECT s.sid, s.sname, p.*
FROM (
    SELECT DISTINCT pid, color
    FROM parts
    GROUP BY pid, color
) AS p
JOIN catalog AS c ON c.pid == p.pid
JOIN suppliers AS s ON s.sid == c.sid

In either case, the surrounding query and joins should work to provide an accurate list for you to work with.

在任何一种情况下，周围的查询和连接都应该能够为您提供一个可以使用的精确列表。

I Hope this helps.

我希望这可以帮助。

-C§

- c§

#1