SQL连接问题

Question

我有一个关于 SQL 连接的有趣问题。我有 2 个表，示例如下:

表 1:ID(整数)，值(字符串)
表2:ID(int), ForeignID(int), (Value)

Table2中的ForeignID字段是Table1中ID的外键。对于表 1 中的给定条目，我在表 2 中有多个条目，如下所示:

Table1:  
ID, Value  
0, "Hello World"  
1, "Bonjour"  

Table2:  
ID, ForeignID, Value  
0, 0, "First entry"  
1, 0, "Second entry"  
2, 1, "Third entry"  

如果我做一个内部连接，例如

SELECT Table1.Value, Table2. Value FROM 
Table1 INNER JOIN Table2 ON Table1.ID = Table2.ForeignID

我会得到

Hello world, First entry  
Hello world, Second entry  
Bonjour, Third entry  

有没有办法只获取表2中的TOP条目，例如:

Hello world, First entry  
Bonjour, Third entry  

Answer 1

这也行:

SELECT  Table1.value
        , Table2.value
FROM    Table1 
        INNER JOIN Table2 ON Table1.id = Table2.foreignID
        INNER JOIN (   
          SELECT    MIN(ID) AS ID, ForeignID
          FROM      Table2
          GROUP BY  ForeignID
        ) MinID ON Table2.foreignid = MinID.foreignid
                   AND Table2.id = MinID.id