blocks|key|628951|text|我对您的其他表做了一些假设。(例如，您有一个我称为posts的帖子表和一个以tag_id作为PK的表，我将其称为tag_table，以避免与posts/tags表的名称冲突，我可以看到您已经调用了tags)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|628952|您希望在列表{1,3}中不存在标签的帖子，也不存在与相应post_id/+tag+_id匹配的记录，因此您可以使用double+not+EXISTS结构，如下所示。|628953|SELECT+post_id
FROM+posts+p
WHERE+NOT+EXISTS+
++++(SELECT+*+FROM+tag_table+tt
++++WHERE+tag_id+IN+(1,3)
++++AND+NOT+EXISTS
++++++++(SELECT+*+FROM+tags+t
++++++++WHERE+t.tag_id+=+tt.tag_id++and
++++++++p.post_id+=+t.post_id)++++++++
++++)|code-block|syntax|javascript|628954|另一种替代方法是使用Group+By和Count。A+review+of+approaches+to+this+problem+is+here。|628955|entityMap|0|LINK|mutability|MUTABLE|url|http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/^0|P|5|1K|9|2Q|4|0|0|0|P|1A|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|D|@$9|16|A|17|1|18]]|E|$]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

I've made some assumptions about your other tables. (i.e. that you have a table for posts that I have called <code>posts</code> and one with tag_id as the PK which I have called <code>tag_table</code> to avoid a nameclash with the posts/tags table that I can see you already call <code>tags</code>) 

You want posts where there does not exist a tag in the list {1,3} for which there does not exist a matching record with the corresponding post_id/tag_id so you can use a double NOT EXISTS construct as below.

<pre><code>SELECT post_id
FROM posts p
WHERE NOT EXISTS 
 (SELECT * FROM tag_table tt
 WHERE tag_id IN (1,3)
 AND NOT EXISTS
 (SELECT * FROM tags t
 WHERE t.tag_id = tt.tag_id and
 p.post_id = t.post_id) 
 )
</code></pre>

Another alternative approach is to use Group By and Count. <a href="http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/" rel="nofollow noreferrer">A review of approaches to this problem is here</a>.

blocks|key|628963|text|您可以尝试自连接(N+tag_id+->+N+join)，但它可能不快|type|unstyled|depth|inlineStyleRanges|entityRanges|data|628964|SELECT+t1.post_id+
FROM+tags+t1+INNER+JOIN+tags+t2+ON+t1.post_id+=+t2.post_id+
WHERE+t1.tag_id+=+1+AND+t2.tag_id+=+3|code-block|syntax|javascript|628965|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could try a self join (N tag_id -> N join) but probably it's not fast

<pre><code>SELECT t1.post_id 
FROM tags t1 INNER JOIN tags t2 ON t1.post_id = t2.post_id 
WHERE t1.tag_id = 1 AND t2.tag_id = 3
</code></pre>

blocks|key|1371647|text|怎么样|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1371648|SELECT+*+
FROM+tags+
WHERE+post_id+in+
++(SELECT+post_id+AS+pid+
+++FROM+tags+
+++WHERE+1+IN+(SELECT+tag_id+FROM+tags+WHERE+post_id+=+pid)+
+++AND+3+IN+(SELECT+tag_id+FROM+tags+WHERE+post_id+=+pid)
++);|code-block|syntax|javascript|1371649|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

How about

<pre><code>SELECT * 
FROM tags 
WHERE post_id in 
 (SELECT post_id AS pid 
 FROM tags 
 WHERE 1 IN (SELECT tag_id FROM tags WHERE post_id = pid) 
 AND 3 IN (SELECT tag_id FROM tags WHERE post_id = pid)
 );
</code></pre>

blocks|key|628983|text|@Keeper的解决方案的版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|628984|SELECT+DISTINCT+t1.post_id+
FROM+tags+t1,+tags+t2
WHERE+
++t1.post_id+=+t2.post_id++AND+
++t1.tag_id+=+1+AND+t2.tag_id+=+3|code-block|syntax|javascript|628985|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

WHERE version of @Keeper's solution

<pre><code>SELECT DISTINCT t1.post_id 
FROM tags t1, tags t2
WHERE 
 t1.post_id = t2.post_id AND 
 t1.tag_id = 1 AND t2.tag_id = 3
</code></pre>

blocks|key|3627642|text|SELECT+post_id
++FROM+(+SELECT+post_id,
++++++++++++++++count(tag_id)+AS+counter
+++++++++++FROM+tags
++++++++++WHERE+tag_id+IN+(1,3)
++++++++++GROUP+BY+post_id
+++++++)
+WHERE+counter+=+2|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3627643|对问题的第二部分使用GROUP_CONCAT()|unstyled|3627644|SELECT+post_id,
+++++++GROUP_CONCAT(tag_id+ORDER+BY+tag_id+ASC+SEPARATOR+',')
++FROM+tags|3627645|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT post_id
 FROM ( SELECT post_id,
 count(tag_id) AS counter
 FROM tags
 WHERE tag_id IN (1,3)
 GROUP BY post_id
 )
 WHERE counter = 2
</code></pre>

Use GROUP_CONCAT() for the second part of your question

<pre><code>SELECT post_id,
 GROUP_CONCAT(tag_id ORDER BY tag_id ASC SEPARATOR ',')
 FROM tags
</code></pre>

blocks|key|1375271|text|如果没有任何唯一约束，请尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1375272|SELECT+post_id+
FROM+tags+
WHERE+tag_id+=+1+OR+tag_id+=+3+
GROUP+BY+post_id+
HAVING+count(DISTINCT+tag_id)+=+2;|code-block|syntax|javascript|1375273|如果尝试仅检测两个tag_id值，则使用此HAVING子句：|offset|length|style|CODE|1375274|HAVING+MIN(tag_id)+<>+MAX(tag_id)|1375275|如果post_id和tag_id都有一个唯一的约束，这也应该是可行的：|1375276|SELECT+post_id+
FROM+tags+
WHERE+tag_id+=+1+OR+tag_id+=+3+
GROUP+BY+post_id+
HAVING+count(*)+=+2;|1375277|entityMap^0|0|0|9|6|L|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

If there aren't any unique constraints try:

<pre><code>SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(DISTINCT tag_id) = 2;
</code></pre>

Or use this <code>HAVING</code> clause, if trying to detect only two <code>tag_id</code> values:

<pre><code>HAVING MIN(tag_id) &lt;&gt; MAX(tag_id)
</code></pre>

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

<pre><code>SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(*) = 2;
</code></pre>

Say there is such table:

<pre><code>mysql&gt; SELECT * FROM tags;
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 2 |
| 1 | 3 |
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
+---------+--------+
5 rows in set (0.00 sec)
</code></pre>

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

Mysql: how to select groups having certain values?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

假设有这样一张表：mysql> SELECT * FROM tags;+---------+--------+| post_id | tag_id |+---------+--------+|       1 |      2 ||       1 |      3 ||       1 |      1 ||    ...

问Mysql:如何选择具有特定值的分组？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql:如何选择具有特定值的分组？EN