blocks|key|5617571|text|我们可以尝试使用带有EXISTS子句的INSERT+INTO+...+SELECT，该子句断言userId为2/3的情况尚未出现在任何组中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5617572|INSERT+INTO+tbl_members+(groupId,+userId)
SELECT+groupId,+userId
FROM+(
++++SELECT+3+AS+groupId,+2+AS+userId+UNION+ALL+SELECT+3,+3
)+t
WHERE+NOT+EXISTS+(SELECT+1+FROM+tbl_members
++++++++++++++++++WHERE+userId+IN+(2,+3)
++++++++++++++++++GROUP+BY+groupId
++++++++++++++++++HAVING+MIN(userId)+<>+MAX(userId));|code-block|syntax|javascript|5617573|您还必须在新组的tbl_group表中插入一条新记录。|5617574|entityMap^0|A|6|J|M|1B|6|0|0|8|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

We can try using <code>INSERT INTO ... SELECT</code> with an <code>EXISTS</code> clause which asserts that <code>userId</code> of 2/3 does not already occur for any group.

<pre><code>INSERT INTO tbl_members (groupId, userId)
SELECT groupId, userId
FROM (
 SELECT 3 AS groupId, 2 AS userId UNION ALL SELECT 3, 3
) t
WHERE NOT EXISTS (SELECT 1 FROM tbl_members
 WHERE userId IN (2, 3)
 GROUP BY groupId
 HAVING MIN(userId) &lt;&gt; MAX(userId));
</code></pre>

You would also have to insert a new record into the <code>tbl_group</code> table for the new group.

blocks|key|4149190|text|SELECT+DISTINCT+groupId+FROM+tbl_members+WHERE+userId+=+'2'
INTERSECT
SELECT+DISTINCT+groupId+FROM+tbl_members+WHERE+userId+=+'3'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4149191|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT DISTINCT groupId FROM tbl_members WHERE userId = '2'
INTERSECT
SELECT DISTINCT groupId FROM tbl_members WHERE userId = '3'
</code></pre>

blocks|key|1832817|text|我认为这个查询将从表中给出userid+(2,3)，groupid+(2,3)中的值组|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1832818|SELECT+count(1),+userid+,groupid++FROM+tbl_members+
WHERE+groupid+IN+(3,2)+and+userid+in+(3,2)
group+by++userid,+groupid;|code-block|syntax|javascript|1832819|entityMap^0|D|6|Q|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

I think this query will give the group of values in <code>userid</code> (2,3), <code>groupid</code> (2,3)
from the table

<pre><code>SELECT count(1), userid ,groupid FROM tbl_members 
WHERE groupid IN (3,2) and userid in (3,2)
group by userid, groupid;
</code></pre>

blocks|key|1518239|text|一种方法是使用带有HAVING子句的GROUP+BY来使用基于条件聚合的过滤。我们将使用一个查询来获取仅具有userId值3和2的groupId值。如果查询没有返回任何这样的值；这意味着不存在这样的group；因此您可以创建该组。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1518240|SELECT
++groupId
FROM+tbl_members+
GROUP+BY+groupId
HAVING+SUM(userId+=+2)+AND+/*+userID+2+exists+*/
+++++++SUM(userId+=+3)+AND+/*+userID+3+exists+*/
+++++++NOT+SUM(userId+NOT+IN+(2,3))+/*+No+other+userId+exists+(other+than+2,3)+*/|code-block|syntax|javascript|1518241|entityMap^0|9|6|I|8|1I|6|1T|7|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

One way is to use conditional aggregation based filtering using <code>GROUP BY</code> with <code>HAVING</code> clause. We will use a query to get the <code>groupId</code> value which has <code>userId</code> values of 3 and 2 only. If the query does not return any such value; this implies that no such Group exists; hence you can create the group.

<pre><code>SELECT
 groupId
FROM tbl_members 
GROUP BY groupId
HAVING SUM(userId = 2) AND /* userID 2 exists */
 SUM(userId = 3) AND /* userID 3 exists */
 NOT SUM(userId NOT IN (2,3)) /* No other userId exists (other than 2,3) */
</code></pre>

blocks|key|5617684|text|这将返回groupId，其中组中只有2和3。组中所有用户if的计数-如果组中没有其他用户if，则userId+=2的记录的总和为1；如果组中没有其他用户if，则3必须为0。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5617685|当然，只有当2和3在同一组中不重复时，这才会起作用。|5617686|SELECT+groupId
FROM+tbl_members
GROUP+BY+groupId
HAVING+COUNT(userId)-SUM(userId+IN+(2,3))+=+0|code-block|syntax|javascript|5617687|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This returns groupId's where only 2 AND 3 are in the group. 
Count of all userId's in the group - sum of 1 for records where userId = 2 or 3 has to be 0 if there are no other userid's in the group.

Sure this will work only if 2 and 3 don't repeat itself in the same group.

<pre><code>SELECT groupId
FROM tbl_members
GROUP BY groupId
HAVING COUNT(userId)-SUM(userId IN (2,3)) = 0
</code></pre>

blocks|key|4149349|text|第一个内部查询将决定所有获取的组只有两个成员，这将减少不必要的记录集，然后第二个内部查询获取该组id并检查该组中是否没有其他成员3,2，最后整个查询将给出只有3,2作为成员的组id。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4149350|SELECT+groupId+
FROM+tbl_members+m
+WHERE+m.userId+IN+(3,2)+AND+
(SELECT+COUNT(memberId)+FROM+tbl_members+mmm+WHERE+mmm.groupId=m.groupId)=2+AND+
(SELECT+COUNT(memberId)+FROM+tbl_members+mm+WHERE+mm.groupId=m.groupId+AND+
m.userId+NOT+IN+(3,2))<=0+
GROUP+BY+groupId|code-block|syntax|javascript|4149351|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|$]]

First inner query will decide all fetched groups have only two members that will reduce set of unnecessary records then second inner query take that group id and check whether there is no other member then 3,2 in that group, and finally whole query will give group id which has only 3,2 as member.

<pre><code>SELECT groupId 
FROM tbl_members m
 WHERE m.userId IN (3,2) AND 
(SELECT COUNT(memberId) FROM tbl_members mmm WHERE mmm.groupId=m.groupId)=2 AND 
(SELECT COUNT(memberId) FROM tbl_members mm WHERE mm.groupId=m.groupId AND 
m.userId NOT IN (3,2))&lt;=0 
GROUP BY groupId
</code></pre>

I have two tables tbl_group AND tbl_members.
Here is snapshot of both the tables.

<pre><code>+----+------------+
| id | groupTitle |
+----+------------+
| 1 | Group 1 |
| 2 | Group 2 |
+----+------------+

+----+---------+--------+
| id | groupId | userId |
+----+---------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
+----+---------+--------+
</code></pre>

Now i want to create one more group with userId 3,2 but before that i want to check that is there any group which has same member 3 and 2 not any other.

i have used IN clause but it does not work.

<pre><code>SELECT DISTINCT groupId 
FROM tbl_members 
WHERE userId IN (3,2)
</code></pre>

It is returning both the group though result would be empty.

How to find exact row match using IN in Where clause?

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我有两张桌子tbl_group和tbl_members。下面是这两个表的快照。+----+------------+| id | groupTitle |+----+------------+|  1 | Group 1    ||  2 | Group 2    |+----+------------++----+-...

问如何在Where子句中使用IN查找精确的行匹配？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Where子句中使用IN查找精确的行匹配？EN