SQL 抓取 X 列中等于 A 的所有记录(如果这些记录中的任何一条在 Y 列中包含 B)

Question

我正在尝试提取一个报告，该报告将获取某个对象的所有活动日志。

本质上，我试图从中汲取：

Object, Activity, User, Timestamp, Group
Object1, Activity1, User, Timestamp, Home
Object1, Activity2, User, Timestamp, Away
Object1, Activity3, User, Timestamp, Away
Object2, Activity1, User, Timestamp, Away
Object2, Activity2, User, Timestamp, Away
Object3, Activity1, User, Timestamp, Home
Object4, Activity1, User, Timestamp, Away    
Object4, Activity1, User, Timestamp, Home

如果任何组列具有"主页"条目，我想提取所有对象记录。我什至想要列中没有"主页"的记录，只要对象记录中的一条记录至少包含一次。

所以例如：

Object1, Activity1, User, Timestamp, Home
Object1, Activity2, User, Timestamp, Away
Object1, Activity3, User, Timestamp, Away
Object3, Activity1, User, Timestamp, Home
Object4, Activity2, User, Timestamp, Away
Object4, Activity3, User, Timestamp, Home

有没有办法使用 MS SQL 完成此操作？

Answer 1

使用子查询/cte查找带有"home"的对象，然后将整个表连接到该对象：

SELECT a.*
FROM YourTable a
JOIN (SELECT DISTINCT [Object]
      FROM YourTable
      WHERE [group] = 'Home'
      )b
ON a.[Object] = b.[Object] 

演示：SQL 小提琴

Answer 2

select *
from table a
where exists(select 1 from table b where a.object = b.object and group = 'Home')

Answer 3

SELECT *
FROM TableName 
WHERE OBJECT IN (SELECT Object
                 FROM TableName
                 WHERE [GROUP] = 'Home')