使用左联接在一对多关系中选择第一条记录 [英] Select first record in a One-to-Many relation using left join

问题描述

我正在尝试使用左联接联接两个表.而且结果集必须只包含正确的"联接表中的第一条记录.

I'm trying to join two tables using a left-join. And the result set has to include only the first record from the "right" joined table.

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

Lets say I have two tables A and B as below;

表"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"的一条匹配记录(与匹配的记录无关)

If you notice my result has only the one matched record from table "B"(doesn't matter what record is matched) after left join (and it is a one to many mapping)

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

I need to pick the first matched record from table B and ignore all other rows.

请帮助！

谢谢

推荐答案

玩了一段时间之后，发现这比我想象的要棘手！假设table_b具有一些唯一的单列(例如，单字段主键)，看起来您可以执行以下操作:

After playing around a bit, this turns out to be trickier than I'd expected! Assuming that table_b has some single column that is unique (say, a single-field primary key), it looks like you can do this:

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
       )
;

这篇关于使用左联接在一对多关系中选择第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！