SQL QUERY在一行中进行多次搜索以从同一表的另一行中查找数据 [英] SQL QUERY multiple search in one row to find data from another row in the same table

问题描述

我需要一些代码方面的帮助

I need some help with some code

我有一个名为"stuff"的数据库表，并且我有以下信息:

I have a database table called "stuff" and I have this info:

+------+-------------+---------------------+
| id   | member_id   |     group_id        |
+------+-------------+---------------------+
| 1    |      11     |         aa          |
+------+-------------+---------------------+
| 2    |      22     |         aa          |
+------+-------------+---------------------+
| 3    |      33     |         aa          |
+------+-------------+---------------------+
| 4    |      44     |         bb          |
+------+-------------+---------------------+
| 5    |      55     |         bb          |
+------+-------------+---------------------+
| 6    |      66     |         bb          |
+------+-------------+---------------------+

如果我从一个组中搜索所有3个成员，则需要找到组ID

I need to find the group id if I search all 3 members from one group

类似:

SELECT group_id 
FROM stuff 
WHERE member_id=11 and member_id=22 and member_id=33 

我知道该查询不是有效值，但我不知道如何使其有效.

I know the query it is not valit but I don`t know how to make it valid.

非常感谢您.

推荐答案

该问题称为Relational Division.

SELECT  group_id
FROM    stuff
WHERE   member_id IN (11,22,33)
GROUP   BY group_id
HAVING  COUNT(*) = 3

• SQLFiddle演示
• SQLFiddle Demo

如果member_id不是每个group_id唯一，则需要具有DISTINCT以便仅计算唯一值.

if member_id is not unique for every group_id, you need to have DISTINCT in order to count only unique values.

SELECT  group_id
FROM    stuff
WHERE   member_id IN (11,22,33)
GROUP   BY group_id
HAVING  COUNT(DISTINCT member_id) = 3

此链接的更多变化形式:

More variations on this link:

• 关系部门的SQL

这篇关于SQL QUERY在一行中进行多次搜索以从同一表的另一行中查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！