如何使用数据库中的父记录获取最后一个子记录

I have database with two tables: Customers (Id PK, LastName) and Orders (Id PK, CustomerId FK, ProductName, Price, etc.)

我有两个表的数据库:客户(Id PK,LastName)和订单(Id PK,CustomerId FK,ProductName,Price等)

I want to retrieve only customer' last orders details together with customer name. I use .NET L2SQL but I think it's SQL question more than LINQ question so I post here SQL query I tried:

我想只检索客户的最后订单详细信息以及客户名称。我使用.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]

Problem is that Orders has more columns (30) and with each column the query gets bigger and slower because I need to add next subqueries.

问题是Orders有更多的列(30),每列的查询变得越来越大,因为我需要添加下一个子查询。

Is there any better way?

有没有更好的方法?

2 个解决方案

#1

In SQL Server 2005 and above:

在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

or this:

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

In SQL Server 2000:

在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
        )

#2

-1

Don't you have a date of column in the orders table something like order_date? Instead of select top you should be able to retrieve using max(order_date)

难道订单表中的列日期与order_date类似吗?而不是选择顶部你应该能够使用max(order_date)检索

My sql is rusty but something like this

我的sql生锈了但是这样的东西

select c.col1,o.col1,o.col2,o.col3 from Customers as c, Orders as o where c.id = o.customerid and max(o.order_date)

#1