在一个语句中选择两个表的连接中的第一行 [英] Select the first row in a join of two tables in one statement

问题描述

我需要从连接表A和B的查询中选择第一行。在表B中存在多个具有相同名称的记录。两个表中的任何一个没有标识符。我不能因为我不拥有数据库而改变方案。

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.

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

这给了我

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

我可以通过将结果添加到带有标识列的临时表中，然后选择每个名称的最小ID来执行此操作。

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.

这里的问题是我需要在一个语句中执行此操作。

The problem here is that I require to do this in one single statement.

推荐答案

这将工作：

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

如果您要选择data1的最小值，并在其data2中，然后使用此变体：

If you want to select the least value of data1 and within it data2, then use this variation:

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.

这篇关于在一个语句中选择两个表的连接中的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！