ID列表上的SQL过滤器记录

Question

我有以下连接表：

personphoto

+----+----------+---------+
| Id | PersonId | PhotoId | 
+----+----------+---------+
|  1 |       10 |       5 |
|  2 |       11 |       8 |
|  3 |       12 |      28 |
|  4 |       10 |      15 |
|  5 |       10 |      28 |
|  6 |       12 |      15 |
+----+----------+---------+

我试图过滤表仅根据personid的

列表返回光照器

例如

我想获得所有与用户10和12相关的照片ID。使用上表，该表应返回以下照片ID

15和28 ..

目前我的努力正在返回15、28、5-但是我不希望返回5，因为人ID 12与照片ID 5

无关

这是我已经尝试过的：

select distinct pe.PhotoId 
from PersonPhoto AS pe
where pe.PersonId IN (10, 12)
GROUP BY pe.PhotoId
select pp.PersonId, pp.PhotoId from PersonPhoto AS pp
where pp.PersonId IN (10, 12)
GROUP BY pp.PersonId, pp.PhotoId
HAVING COUNT(DISTINCT pp.PhotoId) = 1

有什么想法？

Answer 1

假设您想获得其中所有所需人员的照片，那么一种方法是使用group by和having：

select pp.PhotoId
from PersonPhoto pp
where pp.PersonId in (10, 12)
group by pp.PhotoId
having count(*) = 2;

如果您可以在表中有重复项，则应在having子句中使用count(distinct PersonId)。

Answer 2

您可能可以像以下那样使用INTERSECT操作员。虽然它在MySQL中无法使用（您的SQL Server标记）

(SELECT DISTINCT PhotoId  FROM PersonPhoto WHERE PersonId = 10)
INTERSECT
(SELECT DISTINCT PhotoId  FROM PersonPhoto WHERE PersonId = 12)

对于MySQL，这将起作用。从mySQL Intersect结果

获取的想法

SELECT DISTINCT PhotoId FROM PersonPhoto
INNER JOIN (SELECT DISTINCT PhotoId  FROM PersonPhoto WHERE PersonId = 10) a USING (PersonId)
INNER JOIN (SELECT DISTINCT PhotoId  FROM PersonPhoto WHERE PersonId = 12) b USING (PersonId)

Answer 3

或使用存在查询。然后，它与您所说的完全读取。

Select distinct photoId    -- get all Photo Ids that     
From @myT a
Where exists (Select * from @myT  -- have the users 10 
              Where photoId = a.photoId
                 and personId = 10)
  and exists (Select * from @myT  -- and 12 associated with them
              Where photoId = a.photoId
                 and personId = 12)