1、cube:生成多維數(shù)據(jù)集，包含各維度可能組合的交叉表格，使用with 關(guān)鍵字連接 with cube
根據(jù)需要使用union all 拼接
判斷 某一列的null值來自源數(shù)據(jù)還是 cube 使用GROUPING關(guān)鍵字
GROUPING([檔案號(hào)]) = 1 ： null值來自cube（代表所有的檔案號(hào)）
GROUPING([檔案號(hào)]) = 0 ： null值來自源數(shù)據(jù)
舉例：
SELECT * INTO ##GETFROM (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 ##GETGROUP 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ì)大家有所幫助。有什么問題可以隨時(shí)留言，小編會(huì)及時(shí)回復(fù)大家的。感謝各位對(duì)本站的支持！
新聞熱點(diǎn)
疑難解答
圖片精選
