SQL查询建议 - 获取以前的客户订单

I have the following table

我有下表

custid    ordid   qty      datesold
1         A2       12      2008-01-05
2         A5       5       2008-01-02
1         A1       5       2008-01-01
2         A7       3       2007-02-05

What't the best way of getting the previous order for every customer?

获得每个客户的先前订单的最佳方式是什么?

Thanks

4 个解决方案

#1

If by "previous" you mean "the one before the latest":

如果用“previous”表示“最新的那个”:

SELECT TOP 1
  ordid
FROM
  orders
WHERE
  custid = @custid
  and datesold < (SELECT MAX(datesold) FROM orders i where i.custid = orders.custid)
ORDER BY
  datesold DESC

Of course datesold has to be a DATETIME with distinct enough values for this to work. A date alone will not be enough. If you have a record created date for example, this would be a good substitution for datesold.

当然,dateold必须是DATETIME,具有足够明显的值才能实现。仅仅一个日期是不够的。例如,如果你有一个记录创建日期,这将是对dateold的一个很好的替代。

#2

A common solution for this kind of problem is to choose the Max(datesold) and get the latest that way. This is ok if the custid/datesold combination is unique, but if there were two orders on the same day, it can cause duplicates.

这种问题的一个常见解决方案是选择Max(datesold)并获得最新的方式。如果custid / dateold组合是唯一的,但是如果同一天有两个订单,则可能会导致重复。

If you have SQL 2005 or higher, you can use the Row_Number function to rank each customers orders and select the first one for each:

如果您有SQL 2005或更高版本,则可以使用Row_Number函数对每个客户订单进行排名,并为每个订单选择第一个:

SELECT custid, ordid, qty, datesold
FROM (
    SELECT *, 
        Row_Number() OVER (PARTITION BY custid ORDER BY datesold desc) as 'Rank'
    FROM tbl
)
WHERE Rank = 1

To make sure it always picks the same item, even if they have the same datesold, add some more items (such as RowID, recieptNumber) into the ORDER BY clause of the Row_number.

为了确保它总是选择相同的项目,即使它们具有相同的dateold,也要在Row_number的ORDER BY子句中添加更多项目(例如RowID,recieptNumber)。

If you don't have SQL 2005, by adding identity column can do similar things:

如果你没有SQL 2005,通过添加标识列可以做类似的事情:

SELECT custid, ordid, qty, datesold
FROM tbl
WHERE id = 
    (SELECT TOP 1 id FROM tbl a WHERE custid = a.custID ORDER BY dateSold)

The downside of this is that there will be a table lookup at least for every customer if not every row.

这样做的缺点是,如果不是每一行,至少对每个客户都会有一个表查找。

If you are lucky you want to get the latest order processed, you could:

如果您很幸运,您希望获得最新的订单处理,您可以:

SELECT custid, ordid, qty, datesold
FROM tbl
INNER JOIN (
    SELECT a.id FROM tbl a GROUP BY tbl.custId
) s ON tbl.id = s.id

#3

Assumption: Datesold will be in a ascending order (previous order's date will be lesser than current one)

假设:Datesold将按升序排列(之前订单的日期将小于当前订单的日期)

Lets say you want to get the Order before A5 for customer 2. Here is how the query can be.

假设您希望在客户2的A5之前获得订单。以下是查询的方式。


SELECT TOP 1 *
FROM Orders
WHERE DateSold < (SELECT DateSold FROM Orders WHERE CustId = 2 and OrdID = A5)
AND CustId = 2

#4

This is very similar to the other question that you asked.

这与您提出的其他问题非常相似。

SELECT
     T1.ordid
FROM
     dbo.Sales T1
INNER JOIN dbo.Sales T2 ON
     T2.custid = T1.custid AND
     T2.datesold > T1.datesold
LEFT OUTER JOIN dbo.Sales T3 ON
     T3.custid = T1.custid AND
     T3.datesold > T1.datesold AND
     T3.datesold < T2.datesold
WHERE
     T1.custid = @custid AND
     T3.custid IS NULL

Same caveat about rows with identical datesold values.

关于具有相同dateold值的行的相同警告。

#1