如何以一对多的关系连接两个表，并且只保留一对一的记录？

I have two tables : Folders and References. A folder can have 0 to 2 references (usually 1 or 2).

我有两个表：文件夹和参考。一个文件夹可以有0到2个引用（通常为1或2）。

I have an existing query that search Folders that respect certains conditions. The query must have a new condition : Find only Folders with one reference.

我有一个现有的查询，搜索尊重某些条件的文件夹。查询必须具有新条件：仅查找具有一个引用的文件夹。

Ideally, to limits change to this old application, the new condition should be within the WHERE clause only (This rule out group by x, having y).

理想情况下，为了限制对此旧应用程序的更改，新条件应仅在WHERE子句中（此规则用x排除组，具有y）。

Ex :

例如：

FolderId Name
0        Folder0
1        Folder1
2        Folder2

RefId FolderId Name
0     1        ref1
1     2        ref2
2     2        ref3

The output should only contain the FolderId 1

输出应该只包含FolderId 1

2 个解决方案

#1

You probably want this:

你可能想要这个：

select . . .
from folders f join
     (select r.*, count(*) over (partition by folderid) as cnt
      from references r
     ) r
     on f.folderid = r.folderid and cnt = 1;

#2

Use the query below to create a temporary table or create a table variable:

使用以下查询创建临时表或创建表变量：

create table #Temp
(
    column_name int
)

insert into #Temp
     SELECT column_name
      FROM table_name
     GROUP BY column_name
    HAVING COUNT(column_name) = 1;

Then use the temp table with join to other tables and place the conditions you want.

然后使用临时表与其他表连接并放置所需的条件。

#1