如何为每一行执行语句并返回整个结果

Question

我有一张桌子，里面有人和他们拥有的汽车

+-------+-------+
| 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以及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

结果：

+-------+-------+
| 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 |
+-------+-------+

我需要以某种方式遍历一个名称列表，找到所有拥有相同汽车的人，然后将其全部合并到一个结果集中。

Answer 1

您可以通过两种方式执行此操作。一种方法是进行复杂的联接。另一种方法是捷径。只需将汽车汇总为一个字符串并比较字符串即可。

with nc as (
      select n.name,
             stuff( (select ',' + t.model
                     from t
                     where t.name = n.name
                     order by t.model
                     for xml path ('')
                    ), 1, 1, '') as cars
      from (select distinct name from t) n
     )
select nc.name, nc.cars, dense_rank() over (order by nc.cars)
from nc
order by nc.cars;

这将创建一个列表，其中包含名称和汽车列表，以逗号分隔列表。如果愿意，可以加入原始表以获取原始行。