MySQL多对多选择

仍在学习MySQL的绳索,我试图找出如何进行涉及多对多的特定选择。如果表名太通用,我深表歉意,我只是在做一些自制的练习。我尽力成为一名自学者。

我有3个表,其中一个是链接表。如何编写

(他们拥有2部手机)的语句。我猜答案在WHERE语句中,但我不知道该怎么写。

-- Table: mark3

+---------+-----------+

| phoneid | name |

+---------+-----------+

| 1 | HTC |

| 2 | Nokia |

| 3 | Samsung |

| 4 | Motorolla |

+---------+-----------+

-- Table: mark4

+------+---------+

| uid | phoneid |

+------+---------+

| 1 | 1 |

| 1 | 2 |

| 2 | 1 |

| 2 | 3 |

| 2 | 4 |

| 3 | 1 |

| 3 | 3 |

+------+---------+

-- Table: mark5

+------+-------+

| uid | name |

+------+-------+

| 1 | John |

| 2 | Paul |

| 3 | Peter |

+------+-------+

回答:

关键是在GROUP BY / HAVING中使用COUNT个DISTINCT电话名称。当计数为2时,您将知道用户同时拥有 电话。

SELECT m5.name

FROM mark5 m5

INNER JOIN mark4 m4

ON m5.uid = m4.uid

INNER JOIN mark3 m3

ON m4.phoneid = m3.phoneid

WHERE m3.name in ('HTC', 'Samsung')

GROUP BY m5.name

HAVING COUNT(DISTINCT m3.name) = 2;

以上是 MySQL多对多选择 的全部内容, 来源链接: utcz.com/qa/402336.html

回到顶部