USE tempdb

IF OBJECT_ID( 'dbo.T1' , 'U'

            )IS NOT NULL

    BEGIN

        DROP TABLE dbo.T1;

    END;

GO

CREATE TABLE dbo.T1

(

id INT ,

productName VARCHAR(200) ,

price MONEY ,

num INT ,

amount INT ,

operatedate DATETIME

)

GO

DECLARE @i INT

DECLARE @rand MONEY

DECLARE @date DATETIME

DECLARE @index INT

DECLARE @DateBase INT

SET @date = GETDATE()

SET @i = 1

WHILE ( @i < 15 )

BEGIN

SET @rand = RAND() * 20

SET @index = CAST(RAND() * 3 AS INT)

SET @DateBase = CAST(RAND() * 10 AS INT)

INSERT  INTO dbo.T1 ( id, productName, price, num, amount, operatedate )

VALUES  ( @i, 'product' + CAST (@index AS VARCHAR(10)), @rand, 100,@rand * 100, @date + @DateBase )

SET @i = @i + 1

 END

SELECT  *  FROM dbo.T1

order by operatedate

--ROLLUP

SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'

               ELSE CONVERT(VARCHAR(10), operatedate, 120)

          END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'

                          ELSE productName

                     END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,

          SUM(amount) AS 金额

  FROM    dbo.T1

GROUP BY ROLLUP(operatedate, productName)

ORDER BY  '日期','产品名称';

--CUBE

SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'

              ELSE CONVERT(VARCHAR(10), operatedate, 120)

         END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'

                         ELSE productName

                    END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,

         SUM(amount) AS 金额

FROM    dbo.T1

GROUP BY CUBE (operatedate, productName)

ORDER BY  '日期','产品名称';