Magical SQL Server aggregate functions and subquery iteration summation

Let's take a look at the following table and the data:

T_product

Figure 1

The table has two fields: XH and Price, where XH is the primary index field, and now you have the following query results:

Figure 2

As you can see from the query above, the rules for Totalprice field values are recorded from 1th to the sum of the current record's price. The value of the Totalprice field for the 3rd record is 10 + 25 + 36 = 71.

Now you want to generate the query results shown in Figure 2 from the data in the T_product table. There may be a lot of readers thinking about using loops and cursors, but that's not very efficient, especially if you're recording a lot of things.

From the analysis of the query results in Figure 2, we know that this result is still a summation operation, but it is not a summation of all the records, nor a grouping sum, but rather an iterative way of summing, the formula is as follows:

Totalprice value of current record = Price value of current record + Totalprice value of previous record

The Totalprice value of the previous record can also be considered as the sum of the price value of all previous records in the current record. Therefore, you can sum each record (using the SUM function), but ask for the current record and the price of the previous record, such as the following SQL statement:

select a.xh, a.price,
(select sum(price) from t_product b where b.xh <= a.xh) as totalprice
from t_product a

As you can see from the SQL statement above, a subquery is used to find the value of the Totalprice field. The basic principle is to calculate the sum of the price value of all records forward from the current record based on the XH value (A.XH) of the current records, B.XH represents the current XH value of the subquery, and in the subquery, the a.xh corresponds to the constant. The query result of the SQL statement above is identical to Figure 2. If our requirement is a price value that does not contain the current record, that is, the formula that calculates the Totalprice field is as follows:

The Totalprice value of the current record = The price value of the previous record + the Totalprice value of the previous record

The Totalprice value of the first record is the price value of the current record, and the results of the query T_product table are shown in Figure 3.

Figure 3