SQLserver中cube：多维数据集实例详解

1、cube:生成多维数据集，包含各维度可能组合的交叉表格，使用with 关键字连接 with cube

根据需要使用union all 拼接

判断 某一列的null值来自源数据还是 cube 使用GROUPING关键字

GROUPING([档案号]) = 1 ： null值来自cube（代表所有的档案号）
GROUPING([档案号]) = 0 ： null值来自源数据

举例：

SELECT * INTO ##GET
FROM
  (SELECT *
    FROM ( SELECT
      CASE
      WHEN (GROUPING([档案号]) = 1) THEN
      '合计'
      ELSE [档案号]      END AS '档案号',
      CASE
      WHEN (GROUPING([系列]) = 1) THEN
      '合计'
      ELSE [系列]      END AS '系列',
      CASE
      WHEN (GROUPING([店长]) = 1) THEN
      '合计'
      ELSE [店长]      END AS '店长', SUM (剩余次数) AS '总剩余',
      CASE
      WHEN (GROUPING([店名]) = 1) THEN
      '合计'
      ELSE [店名]      END AS '店名'
    FROM ##PudianCard
    GROUP BY [档案号], [店名], [店长], [系列]    WITH cube
    HAVING GROUPING([店名]) != 1
        AND GROUPING([档案号]) = 1 --AND GROUPING([系列]) = 1 ) AS M
    UNION
    ALL
      (SELECT *
        FROM ( SELECT
          CASE
          WHEN (GROUPING([档案号]) = 1) THEN
          '合计'
          ELSE [档案号]          END AS '档案号',
          CASE
          WHEN (GROUPING([系列]) = 1) THEN
          '合计'
          ELSE [系列]          END AS '系列',
          CASE
          WHEN (GROUPING([店长]) = 1) THEN
          '合计'
          ELSE [店长]          END AS '店长', SUM (剩余次数) AS '总剩余',
          CASE
          WHEN (GROUPING([店名]) = 1) THEN
          '合计'
          ELSE [店名]          END AS '店名'
        FROM ##PudianCard
        GROUP BY [档案号], [店名], [店长], [系列]        WITH cube
        HAVING GROUPING([店名]) != 1
            AND GROUPING([店长]) != 1 ) AS P )
        UNION
        ALL
          (SELECT *
            FROM ( SELECT
              CASE
              WHEN (GROUPING([档案号]) = 1) THEN
              '合计'
              ELSE [档案号]              END AS '档案号',
              CASE
              WHEN (GROUPING([系列]) = 1) THEN
              '合计'
              ELSE [系列]              END AS '系列',
              CASE
              WHEN (GROUPING([店长]) = 1) THEN
              '合计'
              ELSE [店长]              END AS '店长', SUM (剩余次数) AS '总剩余',
              CASE
              WHEN (GROUPING([店名]) = 1) THEN
              '合计'
              ELSE [店名]              END AS '店名'
            FROM ##PudianCard
            GROUP BY [档案号], [店名], [店长], [系列]            WITH cube								 
 1/3    1 2 3 下一页 尾页