[SQL]CUBEとROLLUPの違いの詳細

3445 ワード

   CUBE,     GROUP BY。

  CUBE ROLLUP     ,     GROUP BY                      ,CUBE           。   GROUP BY    N      N      ,SQLSERVER        2 N-1       。



CUBE ROLLUP       :     

    

CUBE                       。  



ROLLUP                        

    

ROLLUP   GROUP BY           ,        

 CUBE               



COPY     ,   ROLLUP



    :



Select cust_id,product_code,sum(qty) as quantity



From invoices Where cust_id IN (4,5)



Group By cust_id, product_code



WITH Rollup Order By cust_id



    :

cust_id         product_code         quantity

-------        --------------         -------

NULL            NULL                      10

4                 5                        3

4                 6                        3

4                 NULL                     6

5                 5                        4

5                 NULL                     4

    4,5         (

    4 5     

    4 6     

    4        (  cust_id=4  )

    5 5     

    5        (  cust_id=5  )



        ROLLUP      CUBE,           

    :

cust_id         product_code         quantity

-------        --------------         -------

NULL            NULL                       10

NULL            5                           7

NULL            6                           3

4               5                           3

4               6                           3

4               NULL                        6

5               5                           4

5               NULL                        4

 2       5     (product_code=5   )

 3       6     (product_code=6