Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of cube cube in SQLserver, which is very detailed and has certain reference value. Friends who are interested must finish it!

1. Cube: generate a cube containing cross tables that may be combined by various dimensions, and connect to with cube using the with keyword

Use union all stitching as needed

Determine whether the null value of a column comes from the source data or whether cube uses the GROUPING keyword

GROUPING ([file number]) = 1: the null value comes from cube (representing all file numbers)

GROUPING ([file number]) = 0: null value comes from source data

For example:

SELECT * INTO # # GETFROM (SELECT * FROM (SELECT CASE WHEN ([file number]) = 1) THEN 'total' ELSE [file number] END AS 'file number', CASE WHEN (GROUPING ([series]) = 1) THEN 'total' ELSE [series] END AS 'series' CASE WHEN (GROUPING ([store manager]) = 1) THEN 'total' ELSE [store manager] END AS 'store manager', SUM (remaining times) AS 'total surplus', CASE WHEN (GROUPING ([store name]) = 1) THEN 'total' ELSE [store name] END AS 'shop name' FROM # # PudianCard GROUP BY [file number], [store name], [store manager] [series] WITH cube HAVING GROUPING ([store name])! = 1 AND GROUPING ([file number]) = 1-- AND GROUPING ([series]) = 1) AS M UNION ALL (SELECT * FROM (SELECT CASE WHEN ([file number]) = 1) THEN 'total' ELSE [file number] END AS 'file number' CASE WHEN (GROUPING ([series]) = 1) THEN 'total' ELSE [series] END AS 'series', CASE WHEN (GROUPING ([store manager]) = 1) THEN 'total' ELSE [store manager] END AS 'store manager', SUM (remaining times) AS 'total surplus' CASE WHEN (GROUPING ([store name]) = 1) THEN 'total' ELSE [shop name] END AS 'shop name' FROM # # PudianCard GROUP BY [file number], [store name], [store manager] [series] WITH cube HAVING GROUPING ([store name])! = 1 AND GROUPING ([store manager])! = 1) AS P) UNION ALL (SELECT * FROM (SELECT CASE WHEN ([file number]) = 1) THEN 'total' ELSE [ File number] END AS 'file number' CASE WHEN (GROUPING ([series]) = 1) THEN 'total' ELSE [series] END AS 'series', CASE WHEN (GROUPING ([store manager]) = 1) THEN 'total' ELSE [store manager] END AS 'store manager' SUM (remainder) AS 'Total surplus', CASE WHEN (GROUPING ([store name]) = 1) THEN 'Total' ELSE [shop name] END AS 'shop name' FROM # # PudianCard GROUP BY [file number], [store name], [store manager] [series] WITH cube HAVING GROUPING ([store name])! = 1 AND GROUPING ([store manager])! = 1) AS W) UNION ALL (SELECT * FROM (SELECT CASE WHEN (GROUPING ([file number]) = 1) THEN 'Total 'ELSE [file number] END AS' file number' CASE WHEN (GROUPING ([series]) = 1) THEN 'Total' ELSE [Series] END AS 'Series' CASE WHEN (GROUPING ([store manager]) = 1) THEN 'total' ELSE [store manager] END AS 'manager', SUM (remaining times) AS 'total surplus' CASE WHEN (GROUPING ([store name]) = 1) THEN 'total' ELSE [shop name] END AS 'shop name' FROM # # PudianCard GROUP BY [file number], [store name], [store manager] [series] WITH cube HAVING GROUPING ([store name]) = 1 AND GROUPING ([store manager]) = 1 AND GROUPING ([file number]) = 1) AS K)) AS T

2. Rollup: the function is similar to cube.

3. Take the data of a column as the column name, load dynamically, use stored procedures, and concatenate strings

DECLARE @ st nvarchar (MAX) =''; SELECT @ st = @ st + 'max (case when [series] =''+ CAST ([series] AS VARCHAR) +''then [total remaining] else null end) as [' + CAST ([series] AS VARCHAR) +'], 'FROM # # GETGROUP BY [series]; print @ st

4. group according to a certain column and build tables respectively.

SELECT 'select ROW_NUMBER () over (order by [card item] desc) as [serial number], [member], [file number], [card item], [remaining times], [employee] [shop name] into'+ ltrim ([store name]) + 'from query where [store name] =''+ [shop name] +''ORDER BY [card entry] desc' FROM query GROUP BY [store name] above are all the contents of the article "sample Analysis of cube cubes in SQLserver" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.