sqlserver——cube：多维数据集
1、cube:生成多维数据集，包含各维度可能组合的交叉表格，使用with 关键字连接 with cube
根据需要使用union all 拼接
判断 某一列的null值来自源数据还是 cube 使用GROUPING关键字
GROUPING([档案号]) = 1 ： null值来自cube（代表所有的档案号）
GROUPING([档案号]) = 0 ： null值来自源数据
举例：
1 SELECT * INTO ##GET 3 FROM 4 ( 5 SELECT 6 * 7 FROM 8 ( 9 SELECT 10 CASE 11 WHEN (GROUPING([档案号]) = 1) THEN 12 '合计' 13 ELSE 14 [档案号] 15 END AS '档案号', 16 CASE 17 WHEN (GROUPING([系列]) = 1) THEN 18 '合计' 19 ELSE 20 [系列] 21 END AS '系列', 22 CASE 23 WHEN (GROUPING([店长]) = 1) THEN 24 '合计' 25 ELSE 26 [店长] 27 END AS '店长', 28 SUM (剩余次数) AS '总剩余', 29 CASE 30 WHEN (GROUPING([店名]) = 1) THEN 31 '合计' 32 ELSE 33 [店名] 34 END AS '店名' 35 FROM 36 ##PudianCard 37 GROUP BY 38 [档案号], 39 [店名], 40 [店长], 41 [系列] WITH cube 42 HAVING 43 GROUPING([店名]) != 1 44 AND GROUPING([档案号]) = 1 --AND GROUPING([系列]) = 1 45 ) AS M 46 UNION ALL 47 ( 48 SELECT 49 * 50 FROM 51 ( 52 SELECT 53 CASE 54 WHEN (GROUPING([档案号]) = 1) THEN 55 '合计' 56 ELSE 57 [档案号] 58 END AS '档案号', 59 CASE 60 WHEN (GROUPING([系列]) = 1) THEN 61 '合计' 62 ELSE 63 [系列] 64 END AS '系列', 65 CASE 66 WHEN (GROUPING([店长]) = 1) THEN 67 '合计' 68 ELSE 69 [店长] 70 END AS '店长', 71 SUM (剩余次数) AS '总剩余', 72 CASE 73 WHEN (GROUPING([店名]) = 1) THEN 74 '合计' 75 ELSE 76 [店名] 77 END AS '店名' 78 FROM 79 ##PudianCard 80 GROUP BY 81 [档案号], 82 [店名], 83 [店长], 84 [系列] WITH cube 85 HAVING 86 GROUPING([店名]) != 1 87 AND GROUPING([店长]) != 1 88 ) AS P 89 ) 90 UNION ALL 91 ( 92 SELECT 93 * 94 FROM 95 ( 96 SELECT 97 CASE 98 WHEN (GROUPING([档案号]) = 1) THEN 99 '合计' 100 ELSE 101 [档案号] 102 END AS '档案号', 103 CASE 104 WHEN (GROUPING([系列]) = 1) THEN 105 '合计' 106 ELSE 107 [系列] 108 END AS '系列', 109 CASE 110 WHEN (GROUPING([店长]) = 1) THEN 111 '合计' 112 ELSE 113 [店长] 114 END AS '店长', 115 SUM (剩余次数) AS '总剩余', 116 CASE 117 WHEN (GROUPING([店名]) = 1) THEN 118 '合计' 119 ELSE 120 [店名] 121 END AS '店名' 122 FROM 123 ##PudianCard 124 GROUP BY 125 [档案号], 126 [店名], 127 [店长], 128 [系列] WITH cube 129 HAVING 130 GROUPING([店名]) != 1 131 AND GROUPING([店长]) != 1 132 ) AS W 133 ) 134 UNION ALL 135 ( 136 SELECT 137 * 138 FROM 139 ( 140 SELECT 141 CASE 142 WHEN (GROUPING([档案号]) = 1) THEN 143 '合计' 144 ELSE 145 [档案号] 146 END AS '档案号', 147 CASE 148 WHEN (GROUPING([系列]) = 1) THEN 149 '合计' 150 ELSE 151 [系列] 152 END AS '系列', 153 CASE 154 WHEN (GROUPING([店长]) = 1) THEN 155 '合计' 156 ELSE 157 [店长] 158 END AS '店长', 159 SUM (剩余次数) AS '总剩余', 160 CASE 161 WHEN (GROUPING([店名]) = 1) THEN 162 '合计' 163 ELSE 164 [店名] 165 END AS '店名' 166 FROM 167 ##PudianCard 168 GROUP BY 169 [档案号], 170 [店名], 171 [店长], 172 [系列] WITH cube 173 HAVING 174 GROUPING([店名]) = 1 175 AND GROUPING([店长]) = 1 176 AND GROUPING([档案号]) = 1 177 ) AS K 178 ) 179 ) AS T
2、rollup:功能跟cube相似
3、将某一列的数据作为列名，动态加载，使用存储过程，拼接字符串
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、根据某一列分组，分别建表
SELECT
'select ROW_NUMBER() over(order by [卡项] desc) as [序号], [会员],[档案号],[卡项],[剩余次数],[员工],[店名] into ' + ltrim([店名]) + ' from 查询 where [店名]=''' + [店名] + ''' ORDER BY [卡项] desc'
FROM
查询
GROUP BY
[店名]
