SQL笔记(10)_2_CUBE,ROLLUP,GROUPING

CUBE

CUBE生成的结果集显示了所选列中值的所有组合的聚合。

ROLLUP

ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。  

GROUPING

当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

SQLSERVER2012实例

USE learnsql;
CREATE TABLE EBook
(
    EBook_NAME VARCHAR(30),
    EBook_PRICE DECIMAL(8,2),
    EBook_PAGES INTEGER,
    EBook_AUTHOR VARCHAR(30)
        
);
INSERT INTO EBOOK VALUES('HTML5', 36.5, 200, 'A');
INSERT INTO EBOOK VALUES('HTML5', 40.5, 300, 'A');
INSERT INTO EBOOK VALUES('HTML5', 60.5, 200, 'A');
INSERT INTO EBOOK VALUES('CSS3', 36.5, 200, 'A');
INSERT INTO EBOOK VALUES('CSS3', 40.5, 200, 'A');
INSERT INTO EBOOK VALUES('CSS3', 36.5, 600, 'A');

INSERT INTO EBOOK VALUES('CSS3', 36.5, 200, 'B'); <span style="color:#ff6666;"> + 16/01/06</span>

SELECT EBook_NAME, EBook_AUTHOR FROM EBOOK;

SELECT EBook_NAME, EBook_AUTHOR, EBook_PAGES
FROM EBOOK
GROUP BY  EBook_NAME, EBook_AUTHOR, EBook_PAGES 

SELECT EBook_NAME, EBook_AUTHOR, EBook_PAGES
FROM EBOOK
GROUP BY  EBook_NAME, EBook_AUTHOR, EBook_PAGES WITH CUBE

SELECT EBook_NAME, EBook_AUTHOR, EBook_PAGES
FROM EBOOK
GROUP BY EBook_AUTHOR, EBook_NAME, EBook_PAGES WITH CUBE

SELECT EBook_NAME, EBook_AUTHOR, EBook_PAGES
FROM EBOOK
GROUP BY  EBook_NAME, EBook_AUTHOR, EBook_PAGES WITH ROLLUP

SELECT EBook_NAME, EBook_AUTHOR, EBook_PAGES
FROM EBOOK
GROUP BY  EBook_AUTHOR, EBook_NAME, EBook_PAGES WITH ROLLUP