blocks|key|285526|text|这对我来说很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|285527|select+ir.[Invoice+Number],+c.[Carrier+Name]
from+
++++(select+ROW_NUMBER()+over+(order+by+i.[Invoice+Number]+asc)+AS+RowNumber,+i.[Invoice+Number],+i.InvoiceKey
++++from+Invoice+i)+AS+ir
left+join+Carriers+c
on+ir.InvoiceKey+=+c.InvoiceKey
where+RowNumber+=+1
union+all
select+ir.[Invoice+Number],+NULL+as+[Carrier+Name]
from+
++++(select+ROW_NUMBER()+over+(order+by+i.[Invoice+Number]+asc)+AS+RowNumber,+i.[Invoice+Number]
++++from+Invoice+i)+AS+ir
where+RowNumber+>+1|code-block|syntax|javascript|285528|或|285529|select+TOP+1+i.[Invoice+Number],+c.[Carrier+Name]
from+Invoice+i
left+join+Carriers+c
on+i.InvoiceKey+=+c.InvoiceKey
union+all
select+ir.[Invoice+Number],+NULL+as+[Carrier+Name]
from+
++++(select+ROW_NUMBER()+over+(order+by+i.[Invoice+Number]+asc)+AS+RowNumber,+i.[Invoice+Number]
++++from+Invoice+i)+AS+ir
where+RowNumber+>+1|285530|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This works for me:

<pre><code>select ir.[Invoice Number], c.[Carrier Name]
from 
 (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number], i.InvoiceKey
 from Invoice i) AS ir
left join Carriers c
on ir.InvoiceKey = c.InvoiceKey
where RowNumber = 1
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
 (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
 from Invoice i) AS ir
where RowNumber &gt; 1
</code></pre>

or

<pre><code>select TOP 1 i.[Invoice Number], c.[Carrier Name]
from Invoice i
left join Carriers c
on i.InvoiceKey = c.InvoiceKey
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
 (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
 from Invoice i) AS ir
where RowNumber &gt; 1
</code></pre>

blocks|key|2997878|text|group+by+carriername+having+max(invoicenumber)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2997879|要获取每张发票的第一个承运商，请执行以下操作：|unstyled|2997880|group+by+invoicenumber+having+max(carriername)
--+substitute+the+column+you+want+to+order+by+for+carrier+name+to+change+which+is+'first'|2997881|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>group by carriername having max(invoicenumber)
</code></pre>

to get the first carrier for each invoice:

<pre><code>group by invoicenumber having max(carriername)
-- substitute the column you want to order by for carrier name to change which is 'first'
</code></pre>

blocks|key|282176|text|;with+cteRowNumber+as+(
++++select+c.InvoiceKey,+c.[Carrier+Name],+ROW_NUMBER()+over+(partition+by+c.InvoiceKey+order+by+c.[Carrier+Name])+as+RowNum
++++++++from+Carriers+c
)
select+i.[Invoice+Number],
+++++++rn.[Carrier+Name]
++++from+Invoice+i
++++++++left+join+cteRowNumber+rn
++++++++++++on+i.InvoiceKey+=+rn.InvoiceKey
++++++++++++++++and+rn.RowNum+=+1|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|282177|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>;with cteRowNumber as (
 select c.InvoiceKey, c.[Carrier Name], ROW_NUMBER() over (partition by c.InvoiceKey order by c.[Carrier Name]) as RowNum
 from Carriers c
)
select i.[Invoice Number],
 rn.[Carrier Name]
 from Invoice i
 left join cteRowNumber rn
 on i.InvoiceKey = rn.InvoiceKey
 and rn.RowNum = 1
</code></pre>

blocks|key|287874|text|这就是我的方法，使用与您的(MySQL风格)稍有不同的语法，但我猜您也可以将其应用到您的解决方案中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|287875|SELECT+i.invoiceNumber,+c.carrierName
FROM+Invoice+as+i
LEFT+JOIN+Carriers+as+c+ON+(c.id+=+(SELECT+id+FROM+Carriers+WHERE+invoiceKey+=+i.invoiceKey+ORDER+BY+id+LIMIT+1))|code-block|syntax|javascript|287876|这将从Invoice中获取所有记录，并将其与来自运营商的一条(或零条)记录连接在一起，特别是具有相同invoiceKey且只有第一条记录的记录。|287877|只要您在Carriers.invoiceKey上有一个索引，这个查询的性能就应该是可以接受的。|287878|塞巴斯蒂安|287879|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This is how I would do it, using a slightly different syntax than yours (MySQL style), but I guess you could apply it to your solution as well:

<pre><code>SELECT i.invoiceNumber, c.carrierName
FROM Invoice as i
LEFT JOIN Carriers as c ON (c.id = (SELECT id FROM Carriers WHERE invoiceKey = i.invoiceKey ORDER BY id LIMIT 1))
</code></pre>

This will take all records from Invoice, and join it with one (or zero) record from Carriers, specifically the record which has the same invoiceKey and only the first one.

As long as you have an index on Carriers.invoiceKey the performance of this query should be acceptable.

Sebastian

blocks|key|2997919|text|假设Carriers有一个名为id的PRIMARY+KEY|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2997920|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
++++++++)|code-block|syntax|javascript|2997921|entityMap^0|2|8|F|2|I|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

Provided that <code>Carriers</code> has a <code>PRIMARY KEY</code> called <code>id</code>:

<pre><code>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
 )
</code></pre>

blocks|key|2997931|text|在这种情况下，我经常使用一种设备，我在这里将其应用于您的示例，并在下文中进行描述：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2997932|SELECT
++i.[Invoice+Number],
++c.[Carrier+Name]
FROM+Invoice+i
++INNER+JOIN+Carriers+c+ON+i.InvoiceKey+=+c.InvoiceKey
++INNER+JOIN+(
++++SELECT+MIN(ID)+AS+ID
++++FROM+Carriers
++++GROUP+BY+InvoiceKey
++)+c_top+ON+c.ID+=+c_top.ID|code-block|syntax|javascript|2997933|我认为，这大概就是Quassnoi发布的内容，只是我尽量避免使用这样的精选TOPs。|2997934|Invoice和Carriers基于它们的链接表达式(在本例中为InvoiceKey)连接。现在，对于同一个InvoiceKey，Carriers可以有多个行，所以我们需要限制输出。这是使用派生表完成的。|offset|length|style|CODE|2997935|派生表根据用于链接两个表的同一表达式(InvoiceKey)对承运商中的行进行分组。|2997936|还有另一种方法:您可以使用IN+(subquery)代替连接派生表，实现同样的效果。也就是说，完整的查询将如下所示：|2997937|SELECT
++i.[Invoice+Number],
++c.[Carrier+Name]
FROM+Invoice+i
++INNER+JOIN+Carriers+c+ON+i.InvoiceKey+=+c.InvoiceKey
++++AND+c.ID+IN+(SELECT+MIN(ID)+FROM+Carriers+GROUP+BY+InvoiceKey)|2997938|entityMap^0|0|0|0|0|7|8|8|W|A|1I|A|1T|8|0|J|A|0|D|D|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@$K|10|L|11|M|N]|$K|12|L|13|M|N]|$K|14|L|15|M|N]|$K|16|L|17|M|N]|$K|18|L|19|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@$K|1B|L|1C|M|N]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1D|8|@$K|1E|L|1F|M|N]]|9|@]|A|$]]|$1|S|3|T|5|D|7|1G|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|V|$]]

In such cases I often employ a device which I here apply to your example and describe below:

<pre><code>SELECT
 i.[Invoice Number],
 c.[Carrier Name]
FROM Invoice i
 INNER JOIN Carriers c ON i.InvoiceKey = c.InvoiceKey
 INNER JOIN (
 SELECT MIN(ID) AS ID
 FROM Carriers
 GROUP BY InvoiceKey
 ) c_top ON c.ID = c_top.ID
</code></pre>

I think, this is roughly what Quassnoi has posted, only I try to avoid using SELECT TOPs like that.

<code>Invoice</code> is joined with <code>Carriers</code> based on their linking expression (<code>InvoiceKey</code> in this case). Now, <code>Carriers</code> can have multiple rows for the same <code>InvoiceKey</code>, so we need to limit the output. And that is done using a derived table.

The derived table groups rows from Carrier based on the same expression that is used for linking the two tables (<code>InvoiceKey</code>).

And there's another way: instead of joining the derived table you could use <code>IN (subquery)</code> with the same effect. That is, the complete query would then look like this:

<pre><code>SELECT
 i.[Invoice Number],
 c.[Carrier Name]
FROM Invoice i
 INNER JOIN Carriers c ON i.InvoiceKey = c.InvoiceKey
 AND c.ID IN (SELECT MIN(ID) FROM Carriers GROUP BY InvoiceKey)
</code></pre>

blocks|key|2997951|text|或者，您也可以使用OUTER+APPLY。请注意，对于未知字段名称，请使用尖括号：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2997952|Select+i.[Invoice+Number],+c.[Carrier+Name],+x.<Carrier_field1>
From+Invoice+i
OUTER+APPLY+
(
++++SELECT+TOP+1
++++FROM+Carriers+c+
++++WHERE+c.[InvoiceKey]+=+i.[InvoiceKey]
++++ORDER+BY+<order+_clause>
)+x|code-block|syntax|javascript|2997953|entityMap^0|9|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Alternatively you could use <code>OUTER APPLY</code> as well.
Please notice the use of angle brackets for unknown field names:

<pre><code>Select i.[Invoice Number], c.[Carrier Name], x.&lt;Carrier_field1&gt;
From Invoice i
OUTER APPLY 
(
 SELECT TOP 1
 FROM Carriers c 
 WHERE c.[InvoiceKey] = i.[InvoiceKey]
 ORDER BY &lt;order _clause&gt;
) x
</code></pre>

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?

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

I guess semantically speaking, what I'm looking for something akin to the concept of <code>Top 1 c.CarrierName Group by InvoiceKey</code> (or what would be the concept of that if that were possible in 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?

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.

How do I join the first row of a subquery?

SQL Server

MySQL

我有一个发票表和一个按键相关的相关数据子表。特别是，对于每个发票，我只对子表中的第一个相关行感兴趣。假设我希望每个发票键都有一个相关的行-我该如何实现？Select i.[Invoice Number],       c.[Carrier Name]From Invoice i    Left Join Carrier...

问如何连接子查询的第一行？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何连接子查询的第一行？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何连接子查询的第一行？
EN