选择同时拥有狗和猫的用户。

Question

我有这张样品表：

CREATE TABLE `dummy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `pet` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(1, 1, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(2, 1, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(3, 2, 'dog');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(4, 2, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(5, 3, 'cat');
INSERT INTO `dummy` (`id`, `userId`, `pet`) VALUES(6, 4, 'dog');

如何用mysql编写以下语句：

• 检索所有拥有一只狗和一只猫的用户
• 检索所有拥有狗或猫的用户
• 检索所有只拥有猫的用户
• 检索所有不拥有猫的用户

编辑:在dbemerlin的帮助下，我有前两个语句的解决方案。下面是它们：

• 检索所有拥有一只狗和一只猫的用户： 通过拥有计数(*)=2的userId从虚拟宠物= 'cat‘或宠物=’狗‘组中选择*
• 检索所有拥有狗或猫的用户： 选择*从虚拟之地宠物=‘猫’或宠物=‘狗’组由userId

我已经找到了3种解决方案：

• 检索所有只拥有猫的用户： 从虚拟位置选择* userId IN (从虚拟的userId中选择userId，宠物= 'cat‘组，按userId)，由有计数(*)=1的userId组 但是阿德里亚诺有一个更好的解决方案： 选择*从虚拟之地宠物= 'cat‘和userId NOT IN (从虚拟宠物选择userId != 'cat')；

但在最后一项声明中仍然存在问题：

• 检索所有不拥有猫的用户： 通过userId从虚拟宠物!= 'cat‘组中选择* 这也没用。我真正需要的是检索所有用户谁没有猫，但可能有其他宠物。

谢谢!

编辑：这不是作业作业。我试图简化这里提出的问题，并孤立这个问题。实际情况是，我试图检索那些点击了两个不同链接(存储为url字符串)等的用户。如果这是作业作业，那么在这里问如何实现这一点有什么错呢？如果我有一个有MySQL知识的朋友，让他告诉我解决方案和解释比在这里问有什么区别？

Answer 1

解决后一个问题的办法可能是：

SELECT * 
FROM dummy 
WHERE pet = 'cat' 
AND userId NOT IN (
    SELECT userId 
    FROM dummy 
    WHERE pet != 'cat'
);

对于那些只养猫的用户。

这使您可以使用单个变量来表示所选宠物的类型。

这里的结果是，根据您发布的数据：

mysql> select * from dummy where pet = 'cat' and userId not in \
    -> (select userId from dummy where pet != 'cat');
+----+--------+-----+
| id | userId | pet |
+----+--------+-----+
|  5 |      3 | cat |
+----+--------+-----+
1 row in set (0.00 sec)

编辑:对于最后一个问题，您只需在选择中反转=和!=。在问这个问题之前，一定要想一想。

编辑:你想知道性能。MySQL提供的一个工具是解释。以关键字EXPLAIN作为查询的前缀将对查询的性能、可能的执行路径、所涉及的键和索引等进行分析。在本例中：

mysql> explain select * from dummy where pet = 'cat' and userId not in (select userId from dummy where pet != 'cat');
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  2 | DEPENDENT SUBQUERY | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain SELECT * FROM dummy WHERE userId IN (SELECT userId FROM dummy WHERE pet = 'cat' GROUP BY userId) GROUP BY userId HAVING COUNT(*) = 1;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | dummy | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.00 sec)

您会注意到，您的查询将“使用临时文件，使用文件”添加到'extra‘列中。简而言之，这意味着效率较低，因为必须创建一个临时表，并且必须进行排序才能计算结果。您可以阅读这个命令来了解更多信息。

Answer 2

SELECT * FROM `dummy`
WHERE `pet` IN ('dog', 'cat')
GROUP BY `userId`
HAVING COUNT(DISTINCT `pet`) == 2

获取有狗或猫的唯一用户列表，但将其限制在至少有两个不同值的pet (将是狗)和猫的用户，因为您已经将其限制在这两个选项上

Answer 3

1：

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'
OR d.pet = 'dog'
GROUP BY user_id
HAVING COUNT(*) = 2

2：

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'
OR d.pet = 'dog'
GROUP BY user_id

3：

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'cat'

4：

SELECT u.user_id
FROM   user u
INNER JOIN dummy d
USING (user_id)
WHERE d.pet = 'dog'