Use SQL Server Aggregate functions and subquery iteration summation

This article is original. If you need to reprint it, please indicate the author and the source. Thank you!

First, let's look at the following table and its data:

T_product

Figure1

The table has two fields:XHAndPrice, WhereXHIs the primary index field, and the following query results must be obtained:

Figure 2

As shown in the preceding query results,TotalpriceThe rule for field values is from1Record to the current recordPrice. For example3RecordTotalpriceThe field value is10 + 25 + 36 = 71.

Now you want to passT_productTable data generation Diagram2The query result. There may be many readers who want to use loops and cursors, but this method is not very efficient, especially in the case of many records.

Slave chart2The query result analysis shows that this result is still a sum operation, but not a sum of all records, or a group sum, but an iteration, the summation formula is as follows:

Current recordTotalpriceValue=Current recordPriceValue+TheTotalpriceValue

TheTotalpriceThe value can also be viewed as the value of all previous records of the current record.PriceThe sum of values. Therefore, you can sum each record (UseSumFunction), but the current record and previous recordPriceAnd, as shown in the following figure.SQLStatement:

Select A. XH, A. Price,
( Select Sum (Price) From T_product B Where B. XH <= A. XH) As Totalprice
From T_product

from the preceding SQL statement, A subquery is used to calculate the value of totalprice field, the basic principle is to XH value (. XH ) to calculate the price sum of values B. XH indicates the current XH value. In the subquery, . XH is equivalent to a constant. The preceding SQL statement query results and charts 2 identical. If our requirement is not to include the price value of the current record, that is, calculate totalprice the formula of the field is as follows:

Current recordTotalpriceValue=ThePriceValue+TheTotalpriceValue

The first recordTotalpriceThe value is the value of the current record.PriceValue, queryT_productTable result3.

Figure3

To query the above records, you only need<=Change<You can,SQLThe statement is as follows:

SelectA. XH, A. Price,
(Select Sum(Price)FromT_product BWhereB. XH<A. XH)AsTotalprice
FromT_product

HoweverSQLThe first of the queried recordsTotalpriceThe field value isNull,4.

Figure4

In orderNullChange10, You can useCaseStatement,SQLThe statement is as follows:

Select XH, price,
( Case When Totalprice Is Null Then Price Else Totalprice End ) As Totalprice
From
( Select A. XH ,( Select Sum (Price) From T_product B Where B. XH < A. XH) As Totalprice, A. Price
From T_product A) x

AboveSQLThe statement has three layers.SelectQuery, the innermost layer is as follows:

Select Sum (Price) From T_product B Where B. XH < A. XH)

Subqueries at the intermediate layer are as follows:

Select A. XH ,( Select Sum (Price) From T_product B Where B. XH < A. XH) As Totalprice, A. Price
From T_product

The outermost layer is of course the wholeSelectStatement.

The precedingSQLAnd the graph is displayed.3The query results are the same.

If the reader does not like writing too longSQL, You can write part of the content to the function,CodeAs follows:

Create Function Mysum ( @ XH Int , @ Price Int ) Returns Int
Begin
Return ( Select  
( Case When Totalprice Is Null Then @ Price Else Totalprice End ) As Totalprice
From ( Select Sum (Price) As Totalprice From T_product Where XH < @ XH ) X)
End

You can use the followingSQLStatement to use this function:

Select XH, price, DBO. mysum (XH, price) As Totalprice
From T_product

The precedingSQLAfter3The query result.

CreateT_productTableSQLStatement (SQL Server 2005) As follows:

Set Ansi_nulls On
Go
Set Quoted_identifier On
Go
If Not Exists ( Select * From SYS. Objects Where Object_id = Object_id (N ' [DBO]. [t_product] ' ) And Type In (N ' U ' ))
Begin
Create Table [ DBO ] . [ T_product ] (
[ XH ] [ Int ] Not Null ,
[ Price ] [ Int ] Not Null ,
  Constraint [ Pk_t_product ] Primary Key Clustered  
(
[ XH ] ASC
) With (Ignore_dup_key = Off ) On [ Primary ]
) On [ Primary ]
End