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

DECLARE @NUM INT;

SET @NUM=1;

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