查找具有匹配行的组

一尘不染

查找具有匹配行的组

sql

我有一张桌子（CarOwners）和他们拥有的汽车的类型

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

给定名字，我如何找到其他人拥有 完全相同的
汽车？例如，如果我以Mark为目标，则没有人只有Civic，因此查询将不返回任何内容。如果我以Lisa为目标，则查询将返回

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

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

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

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的人，这不是我想要的。

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

阅读 135

2021-03-17

共1个答案

一尘不染

这将name使用带有的公共表表达式（cte）计算每个行的行数count()
over() 。

然后matchescte使用一个自联接，其中名称不匹配，模型匹配，每个名称匹配的模型计数，并且其中一个名称是'Lisa'。该having子句可确保匹配的行数（count(*)）与具有的模型数匹配name。

matches本身只会返回name每个人的，因此我们返回源表t以获取每个匹配项的完整模型列表。

;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

extrester演示：http：//rextester.com/SUKP78304

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

我们也可以不使用ctes来编写它，但是这样会使它变得更难遵循：

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

2021-03-17