MySQL多对多选择 [英] MySQL Many-To-Many Select
问题描述
仍在学习MySQL的绳索,我试图找出如何进行涉及多对多选择的特定选择.如果表名太通用,我深表歉意,我只是在做一些自制的练习.我会尽力成为一名自学者.
Still learning the MySQL ropes and I'm trying to find out how to do a specific selection involving many-to-many. I apologize if the table names are too generic, I was just doing some self-made exercises. I try my best to be a self-learner.
我有3个表,其中一个是链接表.如何写显示哪些用户同时拥有HTC和Samsung手机" (他们拥有2部手机)的语句.我猜答案在WHERE语句中,但我不知道该怎么写.
I have 3 tables one of which is a linking table. How do I write the statement which says "Show which users own both an HTC and a Samsung phone" (they own 2 phones). I'm guessing the answer is in the WHERE statement but I can't figure out how to word it.
-- 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时,您将知道该用户同时拥有两个手机.
The key is in the GROUP BY/HAVING using a COUNT of DISTINCT phone names. When the count is 2, you'll know the user has both phones.
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多对多选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!