Mysql:如何选择具有特定值的组?
说有这样的表:
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_ids，所以在这个例子中它只有 1.我想到了类似的东西SELECT post_id FROM tags GROUP BY post_id HAVING ... 之后我想列出这个组中存在的 tag_ids.我该怎么做?
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;
或者使用这个 HAVING 子句，如果试图只检测两个 tag_id 值:
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;
相关文章