如何加入子查询的第一行? [英] How do I join the first row of a subquery?

问题描述

我有一张发票表和一个键相关数据的子表.特别是，对于每张发票，我只对子表中的第一个相关行感兴趣.鉴于我希望每个发票键都有一个相关行 - 我该如何实现?

I've got a table of invoices and a child table of related data related by key. In particular, for each invoice, I'm interested in only the first related row from the child table. Given that I want the one related row for every invoice key - how do I accomplish this?

Select i.[Invoice Number],
       c.[Carrier Name]
From Invoice i
    Left Join Carriers c on i.[InvoiceKey] = c.[InvoiceKey]
Where -- what?

我想从语义上讲，我正在寻找类似于 Top 1 c.CarrierName Group by InvoiceKey 的概念(或者如果在 T 中可能的话，它的概念是什么?-SQL.)

I guess semantically speaking, what I'm looking for something akin to the concept of Top 1 c.CarrierName Group by InvoiceKey (or what would be the concept of that if that were possible in T-SQL.)

我想过对子查询进行左连接，但这似乎效率不高.有没有人有任何 T-SQL 技巧可以有效地实现这一目标?

I've thought about doing a left join on a subquery, but that doesn't seem very efficient. Does anyone have any T-SQL tricks to achieve this efficiently?

编辑:抱歉各位，我忘了提到这是 SQL Server 2000，所以虽然我要为当前的 SQL Server 2005/2008 响应投赞成票，但我可以恐怕不能接受他们.

Edit: Sorry guys, I forgot to mention this is SQL Server 2000, so while I'm going to give upvotes for the current SQL Server 2005/2008 responses that will work, I can't accept them I'm afraid.

推荐答案

前提是 Carriers 有一个名为 id 的 PRIMARY KEY:

Provided that Carriers has a PRIMARY KEY called id:

SELECT  i.[Invoice Number],
        c.[Carrier Name]
FROM    Invoice i
JOIN    Carriers c
ON      c.id = 
        (
        SELECT  TOP 1 ID
        FROM    Carriers ci
        WHERE   ci.InvoiceKey = i.InvoiceKey
        ORDER BY
                id -- or whatever
        )

这篇关于如何加入子查询的第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！