SQL 在一个语句中选择两个表的连接中的第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/774769/
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 the first row in a join of two tables in one statement
提问by Oscar Cabrero
I need to select only the first row from a query that joins tables A and B. On table B exist multiple records with same name. There are not identifiers in any of the two tables. I cannot change the scheme either because I do not own the DB.
我只需要从连接表 A 和 B 的查询中选择第一行。在表 B 上存在多个具有相同名称的记录。两个表中的任何一个中都没有标识符。我也无法更改方案，因为我不拥有数据库。
TABLE A
NAME
TABLE B
NAME
DATA1
DATA2
Select Distinct A.NAME,B.DATA1,B.DATA2
From A
Inner Join B on A.NAME = B.NAME
This gives me
这给了我
NAME DATA1 DATA2
sameName 1 2
sameName 1 3
otherName 5 7
otherName 8 9
but I need to retrieve only one row per name
但我只需要检索每个名称的一行
NAME DATA1 DATA2
sameName 1 2
otherName 5 7
I was able to do this by adding the result into a temp table with a identity column and then select the minimum id per name.
我能够通过将结果添加到一个带有标识列的临时表中，然后选择每个名称的最小 id 来做到这一点。
The problem here is that I require to do this in one single statement.
这里的问题是我需要在一个语句中完成此操作。
回答by SO User
This will work:
这将起作用：
with temp as (
select A.NAME, B.DATA1, B.DATA2,
row_number() over (partition by A.NAME order by A.NAME) as rownum
from TABLEA A inner join TABLEB B
on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1
If you want to select the least value of data1 and within it data2, then use this variation:
如果要选择 data1 和其中 data2 的最小值，请使用以下变体：
with temp as (
select A.NAME, B.DATA1, B.DATA2,
row_number() over (partition by A.NAME order by B.DATA1, B.DATA2) as rownum
from TABLEA A inner join TABLEB B
on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1
Both the queries will give one row per name.
两个查询都会为每个名称提供一行。
回答by Chris Thornhill
Using a GROUP BY may get you part way there, but beware. If you do something like this:
使用 GROUP BY 可能会让你走到这一步，但要小心。如果你做这样的事情：
Select A.NAME, min(B.DATA1), min(B.DATA2)
From A Inner Join B on A.NAME = B.NAME
Group by A.NAME;
You will get the result you are looking for:
你会得到你想要的结果：
NAME DATA1 DATA2
sameName 1 2
otherName 5 7
But only because of the data you are testing with. If you change the data, so that instead of:
但仅仅是因为您正在测试的数据。如果您更改数据，则不是：
otherName 8 9
you had:
你有过：
otherName 8 4
It would return:
它会返回：
NAME DATA1 DATA2
sameName 1 2
otherName 5 4
Note that otherName does not return DATA1 and DATA2 from the same record!
请注意，otherName 不会从同一记录中返回 DATA1 和 DATA2！
Update: A self-join with a comparison on one of the data values may help you, such as:
更新：对其中一个数据值进行比较的自联接可能会对您有所帮助，例如：
SELECT a.*, b.* FROM a,b
LEFT JOIN b b2 ON b.name = b2.name AND b.data2 < b2.data2
WHERE a.name = b.name AND b2.data2 IS NOT NULL;
However, this will only work if the values in DATA2 are unique per NAME.
但是，这仅在 DATA2 中的值对于每个 NAME 都是唯一的时才有效。
回答by Tanktalus
If you can add to a temp table and then query from that, you can do it in one go.
如果您可以添加到临时表，然后从中查询，则可以一次性完成。
WITH T AS (temp table select), RN AS (select min row-numbers from T) SELECT T.NAME, T.DATA1, T.DATA2 FROM T INNER JOIN RN on T.row_number = RN.row_number
There are many other ways to write this, but that's how I've been doing similar things.
有很多其他方法可以写这个，但这就是我一直在做类似事情的方式。
回答by wqw
Try to dedupe B like this
尝试像这样对 B 进行重复数据删除
SELECT A.NAME, bb.DATA1, bb.DATA2
FROM A
JOIN B bb
ON A.NAME = B.NAME
WHERE NOT EXISTS (SELECT *
FROM B
WHERE NAME = bb.NAME
AND (DATA1 > bb.DATA1
OR DATA1 = bb.DATA1 AND DATA2 > bb.DATA2))
Add more OR clauses if more DATAx columns exist.
如果存在更多 DATAx 列，则添加更多 OR 子句。
If A contains duplicates too, simply use DISTINCT as in the OP.
如果 A 也包含重复项，只需像在 OP 中一样使用 DISTINCT。
回答by Brett Bender
Not sure if this will solve your problem or not, but you could try using the GROUP BY clause and group by one of the name columns.
不确定这是否能解决您的问题，但您可以尝试使用 GROUP BY 子句并按名称列之一进行分组。
回答by ingredient_15939
SELECT A.NAME, bb.DATA1, bb.DATA2
From A Inner Join B on A.NAME = B.NAME
WHERE B.DATA1 = (SELECT MIN(DATA1) FROM B WHERE NAME = A.NAME)
This will give your desired result, providing B.DATA1 values are uniquewithin the set relating to table A.
这将给出您想要的结果，前提是 B.DATA1 值在与表 A 相关的集合中是唯一的。
If they're not unique, the only other way I know is using CROSS APPLY in MSSQL 2005 and above.
如果它们不是唯一的，我知道的唯一其他方法是在 MSSQL 2005 及更高版本中使用 CROSS APPLY。
回答by Healley Monteiro
The tag of this question indicates that it would be a solution for DB2, but this is very similar to MS-SQL server, if so try these solutions:
这个问题的标签表明它是 DB2 的解决方案，但这与 MS-SQL 服务器非常相似，如果是，请尝试以下解决方案：
Using CROSS, it will be possible to display what exists only in both tables
使用 CROSS，可以显示只存在于两个表中的内容
select A.*, B.DATA1, B.DATA2
from A
cross apply (select top 1 * from B where B.name = A.name) B
But it is possible to change to OUTER to display what exists in A without the obligation to exist in B
但是可以改为 OUTER 来显示 A 中存在的内容，而不必存在 B 中
select A.*, B.DATA1, B.DATA2
from A
OUTER apply (select top 1 * from B where B.name = A.name) B
In the structure of the apply statement, it would also be possible to include an ORDER statement, since there is no indication of the order of exits in table B
在 apply 语句的结构中，也可以包含 ORDER 语句，因为表 B 中没有指示退出顺序
回答by Rams
You can use row number to get one row for each name, try something like below
您可以使用行号为每个名称获取一行，请尝试以下操作
Select name,data1,data2 from
(Select A.NAME,B.DATA1,B.DATA2,row_number() over(partitioj by a.name order by a.name) rn
From A
Inner Join B on A.NAME = B.NAME) where rn=1
