选择同时拥有狗和猫的用户 [英] Select users who own both a dog and a cat
问题描述
我有这个示例表:
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中编写以下语句:
How can I write the statements below in mysql:
- 检索同时拥有狗和猫的所有用户
- 检索所有拥有狗或猫的用户
- 检索所有只拥有一只猫的用户
- 检索所有没有猫的用户
在 dbemerlin 的帮助下，我对前两个语句有了解决方案.他们在这里:
With the help of dbemerlin, I have solutions for first two statements. Here they are:
检索同时拥有狗和猫的所有用户:
Retrieve all users who own both a dog and a cat:
SELECT * FROM dummy WHERE pet = 'cat' OR pet = 'dog' GROUP BY userId HAVING COUNT(*) = 2
检索所有拥有狗或猫的用户:
Retrieve all users who own a dog or a cat:
SELECT * FROM dummy WHERE pet = 'cat' OR pet = 'dog' GROUP BY userId
我找到了解决方案 3:
I have found a solution for 3:
检索所有只拥有一只猫的用户:
Retrieve all users who own only a cat:
SELECT * FROM dummy WHERE userId IN (SELECT userId FROM dummy WHERE pet = 'cat' GROUP BY userId) GROUP BY userId HAVING COUNT(*) = 1
但阿德里亚诺有一个更好的解决方案:
But Adriano have a better solution:
SELECT * FROM dummy WHERE pet = 'cat' AND userId NOT IN (SELECT userId FROM dummy WHERE pet != 'cat');
但最后一条语句仍有问题:
But still having problems for the last statement:
检索所有没有猫的用户:
Retrieve all users who doesn't own a cat:
SELECT * FROM dummy WHERE pet != 'cat' GROUP BY userId
这也行不通.我真正需要的是检索所有没有猫但可能有其他宠物的用户.
This doesn't work either. What I exactly need is that to retrieve all user who doesn't own a cat but may have other pets.
谢谢！
这不是家庭作业.我试图简化这里提出的问题并隔离问题.实际情况是我试图检索点击了 2 个不同链接(存储为 url 字符串)等的用户.如果这是一项家庭作业，那么在这里询问如何实现这一点有什么问题?如果我的朋友有 MySQL 知识，请他告诉我解决方案和解释与在这里询问有什么区别?
This is not a homework assignment. I tried to simplify the question for asking here and also to isolate the problem. The real situation is I am trying to retrieve users who have click 2 different links (stored as url strings) and etc. And if this was a homework assignment, what's the wrong in asking how to achieve this here? If I had a friend of mine who had MySQL knowledge, what's the difference asking him to tell me the solution and explain than asking here?
推荐答案
后一个问题的一个解决方案可能是:
One solution to your latter problems could be this:
SELECT *
FROM dummy
WHERE pet = 'cat'
AND userId NOT IN (
SELECT userId
FROM dummy
WHERE pet != 'cat'
);
适用于只有猫的用户.
这让您可以使用单个变量来表示您想要选择的宠物类型.
This lets you use a single variable to represent the type of pet you want selected.
这里的结果，以及您发布的数据:
The result here, with the data you posted:
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)
对于最后一个问题，您只需反转选择中的 = 和 != 即可.在询问之前，请先考虑一下.
For your last problem, you just reverse the = and != in the selects. Do try to think about it for a second before asking.
您想了解性能.MySQL 提供的一种工具是 EXPLAIN.使用关键字 EXPLAIN 作为查询前缀将为您分析其性能、可能的执行途径、涉及的键和索引等.在这种情况下:
You want to know about performance. One tool offered by MySQL is EXPLAIN. Prefixing your query with the keyword EXPLAIN will give you an analysis of its performance, possible pathway of execution, keys and indexes involved, etc. In this case:
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)
您会注意到您的查询在额外"列中添加了使用临时文件排序".简而言之，这意味着它的效率较低，因为必须创建一个临时表，并且必须进行排序才能计算结果.您可以阅读此手册页以了解更多信息.
You'll notice that your query adds a "using temporary, using filesort" to the 'extra' column. That, in brief, means it is less efficient, because a temporary table must be created, and sorting must occur for your result to be calculated. You can read this manpage to know more.
这篇关于选择同时拥有狗和猫的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
