这是上一个问题的延续:查找具有匹配行的组 https://stackoverflow.com/questions/42700771/find-groups-with-matching-rows
我有一张表,其中包含人和他们拥有的汽车
+-------+-------+
| Name | Model |
+-------+-------+
| Bob | Camry |
| Bob | Civic |
| Bob | Prius |
| John | Camry |
| John | Civic |
| John | Prius |
| Kevin | Civic |
| Kevin | Focus |
| Mark | Civic |
| Lisa | Focus |
| Lisa | Civic |
+-------+-------+
这个查询给了我所有拥有完全相同汽车的人Lisa
以及丽莎本人,这很好。
;with cte as (
select *
, cnt = count(*) over (partition by name)
from t
)
, matches as (
select x2.name
from cte as x
inner join cte as x2
on x.model = x2.model
and x.cnt = x2.cnt
and x.name = 'Lisa'
group by x2.name, x.cnt
having count(*) = x.cnt
)
select t.*
from t
inner join matches m
on t.name = m.name
Result:
+-------+-------+
| name | model |
+-------+-------+
| Lisa | Civic |
| Lisa | Focus |
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+
如果我想找到所有拥有相同汽车的人Bob
,我重新运行查询,结果应该给我John
.
现在,我有一个 Java 名称列表,对于每个名称,我运行此查询。真的很慢。无论如何,是否可以在单个数据库调用中找到所有拥有相同汽车的人并将结果分组?
例如,使用第一个表。我可以运行一个查询来对名称进行分组。注意如何Mark
已经消失了,因为他不拥有与其他人完全相同的汽车,而只是其中的一个子集。
+-------+-------+-------+
| Name | Model | Group |
+-------+-------+-------+
| Bob | Camry | 1 |
| Bob | Civic | 1 |
| Bob | Prius | 1 |
| John | Camry | 1 |
| John | Civic | 1 |
| John | Prius | 1 |
| Kevin | Civic | 2 |
| Kevin | Focus | 2 |
| Lisa | Focus | 2 |
| Lisa | Civic | 2 |
+-------+-------+-------+
这个结果集也很好,我只需要知道谁属于哪个组,我可以稍后取他们的车。
+-------+-------+
| Name | Group |
+-------+-------+
| Bob | 1 |
| John | 1 |
| Kevin | 2 |
| Lisa | 2 |
+-------+-------+
我需要以某种方式遍历姓名列表并找到拥有相同汽车的所有人,然后将其全部合并到一个结果集中。