SQL选择最后一次出现的行和计数出现次数

Question

我有一个表OrderSpecs。我需要选择OrderSpecs中的所有行customer ID，但仅选择order ID每个订单的最后一次出现（通过时间戳记）。我还想让我的查询计算它为每个OrderID找到的发生次数。

这里是我的表（浓缩至仅显示关键信息）：

OrderSpecs选择按日期和CustomerID最近为了行时

ID  OrderID CustomerID  CreatedDate     SpecDocument 
1  1   5     01/08/2012 11:00:00   *Amendment1* 
2  1   5     02/08/2012 15:32:41   *Amendment2* 
3  2   31    04/08/2012 16:19:00   *Amendment1* 
4  3   5     05/08/2012 12:10:12   *Amendment1* 
5  4   10    08/08/2012 09:32:56   *Amendment1* 
6  1   5     09/08/2012 11:47:02   *Amendment3* 

我的查询工作正常：

SELECT 
    ID, CustomerID, EstimateNo, OrderYear, OrderNo, ProductionNo, AddedBy,  
    AddedDate, SizeLength, SizeWidth, HomeModelID, HomeTypeID, DrawingNo, 
    CustomerReference, BuildPieces, ProductionPieces, Notes, SpecDocument, OrderID 
FROM 
    OrderSpecs AS o 
WHERE 
    (AddedDate = (SELECT MAX(AddedDate) AS Expr1 
        FROM OrderSpecs AS i 
        WHERE (o.OrderID = OrderID))) 
    AND (CustomerID = @CustomerID) 

但是，我无法计算出如何计算一个occourances的数量每个OrderID。

例如，我想我的产出表是这样的（由CustomerID = 5搜索）：

ID  OrderID  CustomerID  CreatedDate   SpecDocument  Count 
6  1   5    09/08/2012 11:47:02 *Amendment3*  3 
4  3   5    05/08/2012 12:10:12 *Amendment1*  1 

Answer 1

应与SELECT（未经测试）一个子查询工作

SELECT 
    ID, CustomerID, EstimateNo, OrderYear, OrderNo, ProductionNo, AddedBy,  
    AddedDate, SizeLength, SizeWidth, HomeModelID, HomeTypeID, DrawingNo, 
    CustomerReference, BuildPieces, ProductionPieces, Notes, SpecDocument, OrderID, 
    (SELECT COUNT(*) FROM OrderSpecs AS os WHERE o.OrderID=os.OrderID) as [Count] 
FROM 
    OrderSpecs AS o 
WHERE 
    (AddedDate = (SELECT MAX(AddedDate) AS Expr1 
        FROM OrderSpecs AS i 
        WHERE (o.OrderID = OrderID))) 
    AND (CustomerID = @CustomerID)