仍在学习MySQL的绳索,我试图找出如何进行涉及多对多的特定选择。如果表名太通用,我深表歉意,我只是在做一些自制的练习。我尽力成为一名自学者。
我有3个表,其中一个是链接表。如何编写 “显示哪些用户同时拥有HTC和Samsung手机” (他们拥有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;