/*
    功能说明：  创建测试
    修改说明：    Create by LY on 2011-09-11
*/
IF EXISTS (SELECT 1
           FROM  SYSOBJECTS
           WHERE  id = OBJECT_ID('Fact_SaleCar')
           AND   type = 'U')
BEGIN
    DROP TABLE Fact_SaleCar
END
GO
CREATE TABLE [dbo].Fact_SaleCar 
(
   SaleCarId             VARCHAR(20)          NOT NULL,
   SaleName             VARCHAR(50)          NULL,
   CheckOutDate         DATETIME             NULL,
   Price                Float                NULL
   CONSTRAINT PK_Fact_SaleCar PRIMARY key (SaleCarId)
);
GO

BEGIN
/*
    功能说明：  用循环加入测试数据
    修改说明：    Create by LY on 2011-09-11
*/

    DECLARE @NUM INT;
    SET @NUM=1;
    
    /*------- 【20万条】---- */
    WHILE @NUM <= 100000
    BEGIN
        INSERT INTO dbo.Fact_SaleCar
        SELECT '商店'+RTRIM(@NUM),'SSS'+RTRIM(@NUM),GETDATE(),@NUM;     
        SET @NUM=@NUM+1;
    END;
END;


SELECT A.SaleCarId,
       Sum(Price)AS Price
FROM   Fact_SaleCar A
       INNER JOIN (SELECT Max(CheckoutDate) AS CheckoutDate,
                          SaleCarId
                   FROM   Fact_SaleCar B
                   GROUP  BY SaleCarId) C
         ON A.SaleCarId = C.SaleCarId
            AND A. CheckoutDate = C.CheckoutDate
GROUP  BY A.SaleCarId

SELECT A.SaleCarId,
       Sum(Price)AS Price
FROM   Fact_SaleCar A
       LEFT JOIN (SELECT Max(CheckoutDate) AS CheckoutDate,
                         SaleCarId
                  FROM   Fact_SaleCar B
                  GROUP  BY SaleCarId) C
         ON A.SaleCarId = C.SaleCarId
            AND A. CheckoutDate = C.CheckoutDate
GROUP  BY A.SaleCarId