Complete parsing of SQL Server database data summary (WITHROLLUP)

At first glance, it seems very easy. Can it be implemented by using groupby? However, if you study it carefully, you will feel that groupby is powerless, and you will have nothing to do with it. So what should we do? Don't worry. SQLServer has already helped us. Next, come with me.

At first glance, it seems very easy to use group? However, if you study it carefully, you will feel that group by is powerless and you will have nothing to do. So what should we do? Don't worry, SQL Server has already helped us. Next, come with me.

Existing table A has the following content:

Now you want to query this format by encoding:

Q: How can this problem be achieved?

At first glance, it seems very easy to use group? However, if you study it carefully, you will feel that group by is powerless and you will have nothing to do. So what should we do? Don't worry, SQL Server has already helped us. Next, come with me.

First, let's take a look:

The ROLLUP operator is useful when generating reports that contain subtotal and aggregate. The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.

The result set generated by the CUBE operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data. Fact data is the data that records individual events. The extension is created on the columns that the user intends to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension.

The CUBE operator is specified in the group by clause of the SELE statement. The statement selection list should contain dimension columns and aggregate function expressions. The dimension column and keyword with cube should be specified for group. The result set contains all possible combinations of values in the dimension column, and the aggregated values in the basic rows that match the combination of these dimension values.

The difference between CUBE and ROLLUP is:

The result set generated by CUBE displays the aggregation of all the combinations of the values in the selected column.

The result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column.

After reading the above passage, what have you realized? If not, then ...... Hey, your understanding is not enough. It's still early to go to the "Three flowers" Summit :). Next let's take a look (note that the answer will be announced soon ):

-- The key is the following WITH ROLLUP

-- Of course, you can also use with cube, but the results will be slightly different.

After reading the above section, you may still find yourself confused. It doesn't matter if you don't understand it. Do it yourself.

First, create A table A mentioned above and input several rows of data;

Next, open your SQL Server Query analyzer, connect the Server that contains the table A you created above, and select the database that contains the table;

Then: Copy the preceding SQL statement, Paste to the query analyzer, press F5, how? What is shown below? Is it the same as below?

-- If you use with cube, there will be two more rows after the result set (if you only enter several rows of data in the example ):

Success! How can there be so many "NULL" values in the results? Ha, don't worry. These rows are exactly the data rows we want to summarize. It's not hard to see:

01 NULL 13 is a summary of the number of all warehouses encoded as 01; 02 NULL 17 is a summary of the number of all warehouses encoded as 02;

NULL 30 is a summary of the number of data rows.

How? What is the answer? Is it easy? Of course, there are still some shortcomings above, that is, there are many "NULL. How can we remove meaningless NULL values? Next we will optimize it.

1. Replace NULL with Grouping

-- Use Case functions appropriately

I will not write it here, that is, replace ALL the above "NULL" values with the "ALL" string.

2. Use the program for further optimization

// We must optimize the results generated by the preceding SQL statement to display the requirements. The following describes the natural language: