如何从数据库中获取父记录的最后子记录

Question

我有一个包含两个表的数据库： Customers（Id PK，LastName） 和 Orders（Id PK，CustomerId FK，ProductName，Price等） 我想要检索客户的最新订单详细信息以及客户名称。 我使用.NET L2SQL，但我认为这是一个SQL问题而不是LINQ问题，所以我在这里发布了我尝试过的SQL查询：

SELECT [t0].[LastName], (
    SELECT [t2].[ProductName]
    FROM (
        SELECT TOP (1) [t1].[ProductName]
        FROM [Orders] AS [t1]
        WHERE [t1].[CustomerId] = [t0].[Id]
        ORDER BY [t1].[Id] DESC
        ) AS [t2]
    ) AS [ProductName], (
    SELECT [t4].[Price]
    FROM (
        SELECT TOP (1) [t3].[Price]
        FROM [Orders] AS [t3]
        WHERE [t3].[CustomerId] = [t0].[Id]
        ORDER BY [t3].[Id] DESC
        ) AS [t4]
    ) AS [Price]
FROM [Customers] AS [t0]

问题是订单（Orders）有更多的列（30），每增加一个子查询，查询语句就会变得更长且更慢。

有没有更好的方法？

- jlp

Answer 1

在SQL Server 2005及以上版本中：

SELECT  *
FROM    (
        SELECT  o.*,
                ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY o.id DESC) rn
        FROM    customers c
        LEFT JOIN
                orders o
        ON      o.customerId = c.id
        ) q
WHERE   rn = 1

或者这样写：

SELECT  *
FROM    customers c
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    orders o
        WHERE   o.customerId = c.id
        ORDER BY
                o.id DESC
        ) o

在 SQL Server 2000 中：

SELECT  *
FROM    customers с
LEFT JOIN
        orders o
ON      o.id = 
        (
        SELECT  TOP 1 id
        FROM    orders oi
        WHERE   oi.customerId = c.id
        ORDER BY
                oi.id DESC
        )

- Quassnoi

---

在SQL 2008中：只有第二个有效，第一个会提示“列 'Id' 已经为 'q' 指定了多次”。 - jlp

@jlp：现在试试。你应该明确指定内部查询中想要的列。 - Quassnoi