SQL Server 2008 R2：WHERE中多个值的完全匹配

I have the following table:

我有下表：

create table person
(
    id int,
    name varchar(20),
    games varchar(20)
);

insert into person values(1,'A','Cricket'),(1,'A','F1'),(2,'B','Cricket'),(3,'C','Cricket'),(3,'C','F1')

Expected result:

预期结果：

id name games
--------------
1   A   Cricket
1   A   F1
3   C   Cricket
3   C   F1

My Try 1:

我的尝试1：

SELECT * FROM person
WHERe games = 'Cricket' 
AND games = 'F1'

Output: Nothing

输出：没什么

My Try 2:

我的尝试2：

SELECT * FROM person
WHERe games IN('Cricket','F1')

Output:

输出：

id  name    games
-------------------
1   A   Cricket
1   A   F1
2   B   Cricket     ---This should not come
3   C   Cricket
3   C   F1

5 个解决方案

#1

First, grouping it by name wise on games and then select which has participate in more than 1 game.

首先，在游戏中按名称对其进行分组，然后选择参与多个游戏的游戏。

select a.* from 
person as a,
(
    select id,count(*) as total 
    from
    person
    where
    games in ('Cricket','F1')
    group by id,name
)as b
where
a.id = b.id
and b.total >=2

output :

输出：

id  name    games
1   A       Cricket
1   A       F1
3   C       Cricket
3   C       F1

#2

SELECT 
*
FROM person a
WHERE 
    games IN('Cricket','F1')    AND
    (
        SELECT COUNT(Id)
        FROM person
        WHERE 
            Id =  a.Id
    ) = 2

Or more universal

或者更普遍

;WITH model AS
(
    SELECT * FROM (VALUES('Cricket'),('F1')) a(Games)
)
SELECT 
    a.*
FROM 
    person a
INNER JOIN
    model   b
ON
    a.games = b.games
WHERE   
    (
        SELECT COUNT(Id)
        FROM person
        WHERE 
            Id =  a.Id
    ) =
    (
        SELECT COUNT(*) 
        FROM model
    )

#3

You are so close :)

你真是太近了:)

SELECT * FROM person
WHERE games IN ('Cricket','F1') AND Name IN ('A', 'C')

#4

One possibility is to use the OVER-clause to count the number of games per id within your defined filter:

一种可能性是使用OVER子句来计算定义的过滤器中每个id的游戏数量：

;WITH t AS
(
    SELECT *,
    COUNT(id) OVER(PARTITION BY id) AS cnt
    FROM person
    WHERE games IN('Cricket','F1')
)
SELECT id, name, games FROM t
WHERE cnt > 1

#5

You could also count where there is more than 1 value (then there is multiple values and then inner join them with the table to match the rows)

您还可以计算有多于1个值的位置（然后有多个值，然后内部将它们与表连接以匹配行）

select x.id,b.games,b.name from (
select id,count(id) as Total from person

group by id
having count(id) > 1
) x
inner join person b on x.id = b.id

Result

结果

#1