SQLserver中cube：多維數(shù)據(jù)集實(shí)例詳解

來(lái)源：本站原創(chuàng)|時(shí)間：2020-01-10|欄目：MsSql|點(diǎn)擊：次

1、cube:生成多維數(shù)據(jù)集，包含各維度可能組合的交叉表格，使用with 關(guān)鍵字連接 with cube

根據(jù)需要使用union all 拼接

判斷某一列的null值來(lái)自源數(shù)據(jù)還是 cube 使用GROUPING關(guān)鍵字

GROUPING([檔案號(hào)]) = 1 ： null值來(lái)自cube（代表所有的檔案號(hào)）
GROUPING([檔案號(hào)]) = 0 ： null值來(lái)自源數(shù)據(jù)

舉例：

SELECT * INTO ##GET
FROM 
  (SELECT *
    FROM ( SELECT
      CASE
      WHEN (GROUPING([檔案號(hào)]) = 1) THEN
      '合計(jì)'
      ELSE [檔案號(hào)]
      END AS '檔案號(hào)',
      CASE
      WHEN (GROUPING([系列]) = 1) THEN
      '合計(jì)'
      ELSE [系列]
      END AS '系列',
      CASE
      WHEN (GROUPING([店長(zhǎng)]) = 1) THEN
      '合計(jì)'
      ELSE [店長(zhǎng)]
      END AS '店長(zhǎng)', SUM (剩余次數(shù)) AS '總剩余',
      CASE
      WHEN (GROUPING([店名]) = 1) THEN
      '合計(jì)'
      ELSE [店名]
      END AS '店名'
    FROM ##PudianCard
    GROUP BY [檔案號(hào)], [店名], [店長(zhǎng)], [系列]
    WITH cube
    HAVING GROUPING([店名]) != 1
        AND GROUPING([檔案號(hào)]) = 1 --AND GROUPING([系列]) = 1 ) AS M
    UNION
    ALL 
      (SELECT *
        FROM ( SELECT
          CASE
          WHEN (GROUPING([檔案號(hào)]) = 1) THEN
          '合計(jì)'
          ELSE [檔案號(hào)]
          END AS '檔案號(hào)',
          CASE
          WHEN (GROUPING([系列]) = 1) THEN
          '合計(jì)'
          ELSE [系列]
          END AS '系列',
          CASE
          WHEN (GROUPING([店長(zhǎng)]) = 1) THEN
          '合計(jì)'
          ELSE [店長(zhǎng)]
          END AS '店長(zhǎng)', SUM (剩余次數(shù)) AS '總剩余',
          CASE
          WHEN (GROUPING([店名]) = 1) THEN
          '合計(jì)'
          ELSE [店名]
          END AS '店名'
        FROM ##PudianCard
        GROUP BY [檔案號(hào)], [店名], [店長(zhǎng)], [系列]
        WITH cube
        HAVING GROUPING([店名]) != 1
            AND GROUPING([店長(zhǎng)]) != 1 ) AS P )
        UNION
        ALL 
          (SELECT *
            FROM ( SELECT
              CASE
              WHEN (GROUPING([檔案號(hào)]) = 1) THEN
              '合計(jì)'
              ELSE [檔案號(hào)]
              END AS '檔案號(hào)',
              CASE
              WHEN (GROUPING([系列]) = 1) THEN
              '合計(jì)'
              ELSE [系列]
              END AS '系列',
              CASE
              WHEN (GROUPING([店長(zhǎng)]) = 1) THEN
              '合計(jì)'
              ELSE [店長(zhǎng)]
              END AS '店長(zhǎng)', SUM (剩余次數(shù)) AS '總剩余',
              CASE
              WHEN (GROUPING([店名]) = 1) THEN
              '合計(jì)'
              ELSE [店名]
              END AS '店名'
            FROM ##PudianCard
            GROUP BY [檔案號(hào)], [店名], [店長(zhǎng)], [系列]
            WITH cube
            HAVING GROUPING([店名]) != 1
                AND GROUPING([店長(zhǎng)]) != 1 ) AS W )
            UNION
            ALL 
              (SELECT *
                FROM ( SELECT
                  CASE
                  WHEN (GROUPING([檔案號(hào)]) = 1) THEN
                  '合計(jì)'
                  ELSE [檔案號(hào)]
                  END AS '檔案號(hào)',
                  CASE
                  WHEN (GROUPING([系列]) = 1) THEN
                  '合計(jì)'
                  ELSE [系列]
                  END AS '系列',
                  CASE
                  WHEN (GROUPING([店長(zhǎng)]) = 1) THEN
                  '合計(jì)'
                  ELSE [店長(zhǎng)]
                  END AS '店長(zhǎng)', SUM (剩余次數(shù)) AS '總剩余',
                  CASE
                  WHEN (GROUPING([店名]) = 1) THEN
                  '合計(jì)'
                  ELSE [店名]
                  END AS '店名'
                FROM ##PudianCard
                GROUP BY [檔案號(hào)], [店名], [店長(zhǎng)], [系列]
                WITH cube
                HAVING GROUPING([店名]) = 1
                    AND GROUPING([店長(zhǎng)]) = 1
                    AND GROUPING([檔案號(hào)]) = 1 ) AS K ) ) AS T

2、rollup:功能跟cube相似

3、將某一列的數(shù)據(jù)作為列名，動(dòng)態(tài)加載，使用存儲(chǔ)過程，拼接字符串

DECLARE @st nvarchar (MAX) = '';SELECT @st =@st + 'max(case when [系列]=''' + CAST ([系列] AS VARCHAR) + ''' then [總剩余] else null end ) as [' + CAST ([系列] AS VARCHAR) + '],'
FROM ##GET
GROUP BY [系列]; print @st;

4、根據(jù)某一列分組，分別建表

SELECT
				'select ROW_NUMBER() over(order by [卡項(xiàng)] desc) as [序號(hào)], [會(huì)員],[檔案號(hào)],[卡項(xiàng)],[剩余次數(shù)],[員工],[店名] into ' + ltrim([店名]) + ' from 查詢 where [店名]=''' + [店名] + ''' ORDER BY [卡項(xiàng)] desc'
		FROM
			查詢
		GROUP BY
			[店名]

總結(jié)

以上就是本文關(guān)于SQLserver中cube：多維數(shù)據(jù)集實(shí)例詳解的全部?jī)?nèi)容，希望對(duì)大家有所幫助。感興趣的朋友可以繼續(xù)參閱：MYSQL子查詢和嵌套查詢優(yōu)化實(shí)例解析、幾個(gè)比較重要的MySQL變量、ORACLE SQL語(yǔ)句優(yōu)化技術(shù)要點(diǎn)解析等，有什么問題可以隨時(shí)留言，小編會(huì)及時(shí)回復(fù)大家的。感謝各位對(duì)本站的支持！

上一篇：SQL Server 全文搜索功能介紹

欄目：MsSql

下一篇：sql存儲(chǔ)過程實(shí)例--動(dòng)態(tài)根據(jù)表數(shù)據(jù)復(fù)制一個(gè)表的數(shù)據(jù)到另一個(gè)表

本文標(biāo)題：SQLserver中cube：多維數(shù)據(jù)集實(shí)例詳解

本文地址：http://mengdiqiu.com.cn/a1/MsSql/10405.html

更多MsSql

欧美大屁股bbbbxxxx,狼人大香伊蕉国产www亚洲,男ji大巴进入女人的视频小说,男人把ji大巴放进女人免费视频,免费情侣作爱视频

MsSql

SQLserver中cube：多維數(shù)據(jù)集實(shí)例詳解

您可能感興趣的文章

閱讀排行

本欄相關(guān)

隨機(jī)閱讀