SQL，连接两个表。 ID 在 table1 中唯一，但在 table2 中重复。从基于 table2 的记录的字段中选择要包含的记录

Question

我正在尝试根据帐户 ID 连接两个表中的所有字段。帐户 ID 在表 1 中是唯一的，但在表 2 中重复。在表 2 中，我只想为每个 ID 提取一条记录。

表格1:

ID...Field1....Field2

1......aa.........bb  
2......cc.........dd  
3......ee.........ff  
4......gg.........hh  

表 2:

ID...Field3....Field4......Field5

1.....123........123...........123  
1.....345........546...........453   
2.....123........123...........123  
3.....123........123...........123  
4.....123........123...........722   
4.....123........123...........333  
4.....123........123...........123 

我需要根据三个字段中的值从表 2 中选择要提取的 ID。逻辑是这样工作的。在重复项中，选择field3中值最高的ID记录，如果都相同，则选择field4中值最高的记录，如果仍然相同，则选择field4中值最高的记录字段 5。

所以最终结果看起来像这样:

ID....Field1.....Field2.....Field3.....5ield4.....Field5   
1........aa..........bb............345.......546.........452   
2........cc..........dd............123.......123.........123   
3........ee..........ff............123.......123.........123   
4........gg..........hh............123.......123.........722  

太感谢了!这个让我难倒了一段时间

Answer 1

jdevelop 有一个好主意，但它没有考虑到 field3 需要优先于 field4 等的事实。

我不相信 Access 中有行号，这会大大简化事情。我已经很长时间没有使用 Access 权限了，所以您可能需要修改下面的内容，但它应该可以让您几乎一直到那里。如果这是一次努力，那么您可能可以排序然后添加一个自动编号字段。如果不:

SELECT a.id, d.field3, d.field4, max(d.field5) as m_field5
FROM (((Table1 a
INNER JOIN (
    select id, max(field3) as m3
    from table2
    group by id
    ) b on a.id = b.id                        )
INNER JOIN (
    select id, field3, max(field4) as m4
    from table2
    group by id, field3
    ) c
    on b.id = c.id
    and b.m3 = c.field3                       )
INNER JOIN table2 d
    on c.id = d.id
    and c.field3 = d.field3
    and c.m4 = d.field4                       )
group by a.id, d.field3, d.field4

注意:根据 dbenham 的建议编辑
注意2:编辑为连接添加括号，因为在进行多个连接时 Access 需要额外的括号

如果一次性努力或者您可以获得行号，您可能会执行以下操作:

select id, field3, field4, field5, rownumber
into #temp_ordered
from table2
order by id, field3, field4, field5

select a.*
from #temp_ordered a
inner join (
    select id, min(rownumber) min_rownumber
    from #temp_ordered 
    group by id
    ) b on a.id = b.id and a.rownumber = b.min_rownumber

最好的，
大卫