使用左连接选择一对多关系中的第一条记录

发布于 2024-12-15 22:44:24 字数 825 浏览 0 评论 0原文

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

假设我有两个表 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 中选择第一个匹配的记录并忽略所有其他行。

请帮忙!

谢谢

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.

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

Table "A"

code | emp_no

101  | 12222
102  | 23333
103  | 34444
104  | 45555
105  | 56666

Table "B"

code | city       | county
101  | Glen Oaks  | Queens
101  | Astoria    | Queens
101  | Flushing   | Queens
102  | Ridgewood  | Brooklyn
103  | Bayside    | New York

Expected Output:

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

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)

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

Please help!

Thanks

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

雪化雨蝶 2024-12-22 22:44:24

经过一番尝试后,发现这比我想象的要棘手!假设 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
       )
;

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
       )
;
楠木可依 2024-12-22 22:44:24

另一个选项: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 上)都要快。

Another option: OUTER APPLY

If supported by the database, OUTER APPLY is an efficient and terse option.

SELECT *
FROM 
    Table_A a
OUTER APPLY
    (SELECT TOP 1 * 
    FROM Table_B b_1
    WHERE b_1.code = a.code
    ) b
;

This results in a left join to the indeterminate first matched record. My tests show it to be quicker than any other posted solution (on MS SQL Server 2012).

甜尕妞 2024-12-22 22:44:24

得票最高的答案对我来说似乎不正确,而且似乎过于复杂。
只需按子查询中表 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)

The highest voted answer does not seem correct to me, and seems overcomplicated.
Just group by the code field on table B in your subquery and select the maximum Id per grouping.

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)
眉黛浅 2024-12-22 22:44:24

如果您使用的是 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

If you are on SQL Server 2005 or later version, you could use ranking to achieve what you want. In particular, ROW_NUMBER() seems to suit your needs nicely:

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

OR

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
晌融 2024-12-22 22:44:24

我修改了 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
           )
;

I modified the answer from ruakh and this seem to work perfectly with 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
           )
;
残疾 2024-12-22 22:44:24

在 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

In Oracle you can do:

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
诠释孤独 2024-12-22 22:44:24

方法如下:

 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)

但重点是您必须在其中放置一些表达式来告诉查询处理器它意味着首先

this is how:

 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]

Hey, if the city and county are unique, then use them

   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)

But the point is you have to put some expression in there to tell the query processor what it means to be first.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文