sql-使用左联接在一对多关系中选择第一条记录

我正在尝试使用左联接联接两个表。 结果集必须仅包含“正确”联接表中的第一条记录。

假设我有两个表A和B，如下所示；

表“ A”

code | emp_no

101 | 12222

102 | 23333

103 | 34444

104 | 45555

105 | 56666

表“ B”

code | city | county

101 | Glen Oaks | Queens

101 | Astoria | Queens

101 | Flushing | Queens

102 | Ridgewood | Brooklyn

103 | Bayside | New York

预期产量：

code | emp_no | city | county

101 | 12222 | Glen Oaks | Queens

102 | 23333 | Ridgewood | Brooklyn

103 | 34444 | Bayside | New York

104 | 45555 | NULL | NULL

105 | 56666 | NULL | NULL

如果您发现我的结果在左联接(它是一对多映射)之后只有来自表“ B”的一条匹配记录(无论匹配的记录是什么)

我需要从表B中选择第一个匹配的记录，并忽略所有其他行。

请帮忙！

谢谢

Sandra asked 2020-08-10T11:15:34Z

7个解决方案

34 votes

玩了一段时间后，结果比我预期的要复杂得多！ 假设table_b具有一些唯一的单列(例如，单字段主键)，看起来您可以这样做：

SELECT table_a.code,

table_a.emp_no,

table_b.city,

table_b.county

FROM table_a

LEFT

JOIN table_b

ON table_b.code = table_a.code

AND table_b.field_that_is_unique =

( SELECT TOP 1

field_that_is_unique

FROM table_b

WHERE table_b.code = table_a.code

)

;

ruakh answered 2020-08-10T11:15:39Z

7 votes

另一种选择：OUTER APPLY

如果数据库支持，OUTER APPLY是一种高效而简洁的选择。

SELECT *

FROM

Table_A a

OUTER APPLY

(SELECT TOP 1 *

FROM Table_B b_1

WHERE b_1.code = a.code

) b

;

这导致到不确定的第一个匹配记录的左连接。 我的测试表明，它比任何其他已发布的解决方案(在MS SQL Server 2012上)都更快。

QuintinDB answered 2020-08-10T11:16:08Z

6 votes

投票最高的答案对我来说似乎并不正确，而且似乎过于复杂。只需对子查询中表B的代码字段进行分组，然后选择每个分组的最大ID。

SELECT

table_a.code,

table_a.emp_no,

table_b.city,

table_b.county

FROM

table_a

LEFT JOIN

table_b

ON table_b.code = table_a.code

AND table_b.field_that_is_unique IN

(SELECT MAX(field_that_is_unique)

FROM table_b

GROUP BY table_b.code)

CShark answered 2020-08-10T11:16:29Z

4 votes

如果您使用的是SQL Server 2005或更高版本，则可以使用排名来实现所需的功能。 特别是ROW_NUMBER()似乎很适合您的需求：

WITH B_ranked AS (

SELECT

*,

rnk = ROW_NUMBER() OVER (PARTITION BY code ORDER BY city)

FROM B

)

SELECT

A.code,

A.emp_no,

B.city,

B.county

FROM A

LEFT JOIN B_ranked AS B ON A.code = B.code AND b.rnk = 1

要么

WITH B_unique_code AS (

select * from(

SELECT

*,

rnk = ROW_NUMBER() OVER (PARTITION BY code ORDER BY city)

FROM B

) AS s

where rnk = 1

)

SELECT

A.code,

A.emp_no,

B.city,

B.county

FROM A

LEFT JOIN B_unique_code AS B ON A.code = B.code

Andriy M answered 2020-08-10T11:16:53Z

2 votes

我从ruakh修改了答案，这似乎与mysql完美配合。

SELECT

table_a.code,

table_a.emp_no,

table_b.city,

table_b.county

FROM table_a a

LEFT JOIN table_b b

ON b.code = a.code

AND b.id = ( SELECT id FROM table_b

WHERE table_b.code = table_a.code

LIMIT 1

)

;

Gayan answered 2020-08-10T11:17:13Z

1 votes

在Oracle中，您可以执行以下操作：

WITH first_b AS (SELECT code, min(rowid) AS rid FROM b GROUP BY code))

SELECT a.code, a.emp_no, b.city, b.county

FROM a

INNER JOIN first_b

ON first_b.code = a.code

INNER JOIN b

ON b.rowid = first_b.rid

kevin cline answered 2020-08-10T11:17:33Z

0 votes

这是这样的：

Select * From TableA a

Left Join TableB b

On b.Code = a.Code

And [Here put criteria predicate that 'defines' what the first record is]

嘿，如果城市和县是唯一的，那就用它们

Select * From TableA a

Left Join TableB b

On b.Code = a.Code

And b.City + b.county =

(Select Min(city + county)

From TableB

Where Code = b.Code)

但关键是您必须在其中放置一些表达式以告诉查询处理器首先是什么意思。

Charles Bretana answered 2020-08-10T11:18:01Z