Use SQL to implement the & quot; subtotal & quot; and & quot; Total & quot; Methods in the statistical report

The customer puts forward the requirement and adds subtotal to a group to summarize the total. I found some SQL statements on the Internet. Not ideal. I decided to write it by myself.
There are three ideas:
1. Many are implemented using GROUPPING and ROLLUP.
Advantage: The implementation code is concise and requires a deep understanding of GROUPPING and ROLLUP.
Disadvantage: earlier SQL Server versions are not supported.

2. Implement the cursor.
Advantage: the logic is concise.
Disadvantages: complexity and inefficiency.

3. Use a temporary table.
Advantages: simple logic and high execution efficiency. SQL is easy to implement.
Disadvantage: memory consumption when the data volume is large.

In three cases, we decided to use the temporary table.
Effect
Raw table TB

Add subtotal, after total effect

SQL statement
Copy codeThe Code is as follows:
Select * into # TB from TB
Select * into # TB1 from # TB where 1 <> 1
Select distinct zcxt into # TBype from # TB order by zcxt
Select identity (int, 1, 1) fid, zcxt into # TBype1 from # TBype
DECLARE @ I int
DECLARE @ k int

Select @ I = COUNT (*) from # TBype
Set @ k = 0
DECLARE @ strfname varchar (50)
WHILE @ k <@ I
BEGIN
Set @ k = @ k + 1
Select @ strfname = zcxt from # TBype1 where fid = @ k
Set IDENTITY_INSERT # TB1 ON
Insert into # TB1 (fid, qldid, fa_cardid, ztbz, fa_name, model, I _number, gzrq, zcyz, ljzj, jz, sybm, zcxt, fa_ljjzzb)
Select fid, qldid, fa_cardid, ztbz, fa_name, model, I _number, gzrq, zcyz, ljzj, jz, sybm, zcxt, fa_ljjzzb from
(
Select * from # TB where zcxt = @ strfname
Union all
Select 0 fid, ''qldid, ''fa_cardid, ''ztbz, 'subtotal 'fa_name, ''model, sum (I _number) as I _number, ''gzrq, sum (CAST (zcyz as money) as zcyz, sum (CAST (ljzj as money) as ljzj, sum (CAST (jz as money) as jz, ''sybm, ''zcxt, Sum (fa_ljjzzb) as fa_ljjzzb
From # TB where zcxt = @ strfname
Group by ztbz
) As B
Set IDENTITY_INSERT # TB1 off
END
Select qldid, fa_cardid, zcxt, fa_name, model, I _number, gzrq, zcyz, ljzj, jz, sybm, ztbz, fa_ljjzzb from # TB1
Union all
Select ''qldid, ''fa_cardid, ''ztbz, 'total 'fa_name, ''model, sum (I _number) as I _number, ''gzrq, sum (CAST (zcyz as money) as zcyz, sum (CAST (ljzj as money) as ljzj, sum (CAST (jz as money) as jz, ''sybm, ''zcxt, Sum (fa_ljjzzb) as fa_ljjzzb
From # TB
Drop table # TB1
Drop table # TBype1
Drop table # TBype
Drop table # TB

Scaling Improvement
It can be rewritten into a general stored procedure for adding aggregate subtotal.