SQL 使用左连接选择一对多关系中的第一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8161438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select first record in a One-to-Many relation using left join
提问by Sandra
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;
假设我有两个表 A 和 B，如下所示；
Table "A"
表“A”
code | emp_no
101 | 12222
102 | 23333
103 | 34444
104 | 45555
105 | 56666
Table "B"
表“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)
如果您注意到我的结果只有左连接后表“B”中的一条匹配记录（与匹配的记录无关）（并且它是一对多映射）
I need to pick the first matched record from table B and ignore all other rows.
我需要从表 B 中选择第一个匹配的记录并忽略所有其他行。
Please help!
请帮忙！
Thanks
谢谢
回答by ruakh
After playing around a bit, this turns out to be trickier than I'd expected! Assuming that table_bhas some single column that is unique (say, a single-field primary key), it looks like you can do this:
在玩了一会儿之后，结果证明这比我预期的要棘手！假设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
)
;
回答by QuintinDB
Another option: OUTER APPLY
另外一个选项： OUTER APPLY
If supported by the database, OUTER APPLYis an efficient and terse option.
如果数据库支持，OUTER APPLY则是一个高效而简洁的选项。
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 indeterminatefirst matched record. My tests show it to be quicker than any other posted solution (on MS SQL Server 2012).
这会导致左连接到不确定的第一个匹配记录。我的测试表明它比任何其他发布的解决方案都快（在 MS SQL Server 2012 上）。
回答by CShark
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.
投票最高的答案对我来说似乎不正确，而且似乎过于复杂。只需按子查询中表 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)
回答by Andriy M
If you are on SQL Server 2005 or later version, you could use rankingto achieve what you want. In particular, ROW_NUMBER()seems to suit your needs nicely:
如果您使用的是 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
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
回答by Gayan
I modified the answer from ruakhand this seem to work perfectly with mysql.
我修改了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
)
;
回答by kevin cline
In Oracle you can do:
在 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
回答by Charles Bretana
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 meansto be first.
但关键是您必须在其中放入一些表达式来告诉查询处理器成为first意味着什么。
