SQL 选择数据，条件为多行 [英] SQL Select Data with condition on multiple rows

问题描述

假设我有一个名为tblTemp"的表，其中包含以下数据

Suppose that I have a table called "tblTemp" with the following data

ID        Name  
1           A  
2           B  
3           C  
4           A  
4           B  
5           A  
5           B  
5           C  
6           C  
6           B

我只想从 A&B 的名称中获取 ID，而不是像下面这样的 A&B&C:

I want to get ID from name of A&B only not A&B&C like below:

4          A 

或

4          B

如何在 sql 中执行此操作?

How can I do like this in sql?

我尝试了以下 sql，但它也返回第 5 行:

I try the following sql but it return row 5 as well:

SELECT     tblTemp.ID, tblTemp.Name
FROM         tblTemp INNER JOIN
                  tblTemp AS tbltemp_1 ON tblTemp.ID = tbltemp_1.ID
WHERE     (tblTemp.Name = 'A') AND (tbltemp_1.Name = 'B')

推荐答案

比较集合的方法之一是取组的数量，通过搜索集合过滤组，看每组的匹配数是否等于原始的匹配数小组成员:

One of the ways to compare sets is to take the count of group, filter groups by search set, and see if number of matches per group equals original number of group members:

select tblTemp.ID
  from tblTemp
 inner join
 (
   select ID,
          count(*) GroupCount
     from tblTemp
    group by ID
   having count(*) = 2
 ) g
   on tblTemp.ID = g.ID
 where tblTemp.Name in ('A', 'B')
 group by tblTemp.Id, g.GroupCount
having count (*) = g.GroupCount

这应该适用于 MySql 和 Sql Server.

This should work on both MySql and Sql Server.

您可以使用此代码@Sql Fiddle.

这篇关于SQL 选择数据，条件为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！