MySQL:如何选择具有特定值的组? [英] Mysql: how to select groups having certain values?

问题描述

说有这样一张桌子:

mysql> SELECT * FROM tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
|       1 |      2 |
|       1 |      3 |
|       1 |      1 |
|       2 |      1 |
|       2 |      2 |
+---------+--------+
5 rows in set (0.00 sec)

字段名称非常不言自明.我想选择同时具有1和3个tag_id的post_id，因此在此示例中，它仅是1.我想到了类似的东西 SELECT post_id FROM tags GROUP BY post_id HAVING ...我想列出该组中存在的tag_id个.我该怎么办?

Field names are pretty self-explanatory. I want to select post_ids that have both 1 and 3 tag_ids, so in this example it's only 1. I thought of something like SELECT post_id FROM tags GROUP BY post_id HAVING ... After having I'd like to list tag_ids that are present in this group. How do I do that?

推荐答案

如果没有任何唯一约束，请尝试:

If there aren't any unique constraints try:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(DISTINCT tag_id) = 2;

或者，如果尝试仅检测两个tag_id值，请使用此HAVING子句:

Or use this HAVING clause, if trying to detect only two tag_id values:

HAVING MIN(tag_id) <> MAX(tag_id)

如果post_id和tag_id都具有唯一约束，那么这也应该起作用:

If post_id and tag_id both have an unique constraint, this should work too:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(*) = 2;

这篇关于MySQL:如何选择具有特定值的组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！