查找具有匹配行的组 [英] Find groups with matching rows

问题描述

我有一个人表(CarOwners)和他们拥有的汽车类型

I have a table of people (CarOwners) and the types of cars they own

+-------+-------+
| Name  | Model |
+-------+-------+
| Bob   | Camry |
| Bob   | Civic |
| Bob   | Prius |
| Kevin | Civic |
| Kevin | Focus |
| Mark  | Civic |
| Lisa  | Focus |
| Lisa  | Civic |
+-------+-------+

给定一个名字，我如何找到拥有完全相同汽车的其他人?例如，如果我的目标是 Mark，那么没有其他人只有 Civic，因此查询不会返回任何内容.如果我的目标是 Lisa，查询将返回

Given a name, how do i find other people with the exact same cars? For example, if i'm targeting Mark, no one else has ONLY a Civic so the query would return nothing. If i'm targeting Lisa, the query would return

+-------+-------+
| Name  | Model |
+-------+-------+
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+

因为凯文拥有与丽莎完全相同的汽车.如果我以 Kevin 为目标，查询将返回 Lisa.

Because Kevin has the exact same cars as Lisa. If i targeted Kevin, the query would return Lisa.

我创建了一个包含目标人员汽车的 cte，但我不确定如何实现完全匹配"要求.我所有的尝试都返回带有子集匹配的结果.

I created a cte which contains my target persons cars, but i'm not sure how to implement the "exact match" requirement. All of my attempts return results with subset matches.

with LisaCars as (
    SELECT Model FROM CarOwners WHERE Name = 'Lisa'
)
SELECT Name, Model
FROM CarOwners
WHERE Model in (SELECT * FROM LisaCars) AND Name != 'Lisa'

此查询将返回所有拥有 Civic 或 Focus 的人，这不是我要找的.

This query would return all people who have either a Civic or a Focus, which isn't what i'm looking for.

+-------+-------+
| Name  | Model |
+-------+-------+
| Bob   | Civic |
| Kevin | Civic |
| Kevin | Focus |
| Mark  | Civic |
+-------+-------+

推荐答案

这使用 常用表表达式(cte) with count() over().

This counts the number of rows for each name using a common table expression(cte) with count() over().

然后 matches cte 使用自连接，其中名称不匹配，模型匹配，每个名称匹配的模型计数，其中一个名称是 'Lisa'.have 子句确保匹配的行数 (count(*)) 与 name 拥有的模型数相匹配.

Then the matches cte uses a self-join where the names do not match, the models match, the count of models for each name match, and one of those names is 'Lisa'. The having clause ensures that count of matched rows (count(*)) matches the number of models that name has.

matches 本身只会返回每个人的name，因此我们连接回源表t 以获取完整列表每场比赛的模型.

matches itself would only return the name of each person, so we join back to the source table t to get the full list of models for each match.

;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.name <> x2.name
      and 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

rextester 演示:http://rextester.com/SUKP78304

rextester demo: http://rextester.com/SUKP78304

返回:

+-------+-------+
| name  | model |
+-------+-------+
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+

---

我们也可以在没有 ctes 的情况下编写它，但这会使它更难理解:

---

We could also write it without the ctes, but it makes it a little harder to follow:

select t.*
from t 
  inner join (
    select x2.Name
    from (
      select *, cnt = count(*) over (partition by name) 
      from t 
      where name='Lisa'
      ) as x
      inner join (
      select *, cnt = count(*) over (partition by name) 
      from t
      ) as x2
        on x.name <> x2.name
       and x.model = x2.model
       and x.cnt   = x2.cnt 
    group by x2.name, x.cnt
    having count(*) = x.cnt
  ) as m 
    on t.name = m.name

这篇关于查找具有匹配行的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！