我有一个发票表和一个按键相关的相关数据子表。特别是,对于每个发票,我只对子表中的第一个相关行感兴趣。假设我希望每个发票键都有一个相关的行-我该如何实现?
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中可以实现的话,那么它的概念会是什么)。
我曾考虑对子查询执行左连接,但这似乎不是很有效。有没有人有什么T-SQL技巧可以有效地实现这一点?
编辑:对不起,伙计们,我忘了说这是SQL Server2000,所以虽然我要对当前SQL Server2005/2008的响应表示赞赏,但我恐怕不能接受它们。
发布于 2011-01-15 00:54:55
假设Carriers
有一个名为id
的PRIMARY KEY
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
)
发布于 2011-01-14 23:07:56
这对我来说很有效:
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
或
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
发布于 2011-01-15 00:22:27
这就是我的方法,使用与您的(MySQL风格)稍有不同的语法,但我猜您也可以将其应用到您的解决方案中:
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))
这将从Invoice中获取所有记录,并将其与来自运营商的一条(或零条)记录连接在一起,特别是具有相同invoiceKey且只有第一条记录的记录。
只要您在Carriers.invoiceKey上有一个索引,这个查询的性能就应该是可以接受的。
塞巴斯蒂安
https://stackoverflow.com/questions/4692419
复制相似问题