SQL -- process the accumulation of rows, and SQL process the accumulation

SQL -- process the accumulation of rows, and SQL process the accumulation

Background:

There is now a report, where the above data is the seller's order data, according to the daily statistics, from the order table to find a seller's order volume every day, then, go to the daily income table to find out the profit amount of the merchant on the current day, And then count all the earnings amount before the current day, And then filter the records for a certain period of time from the table.

The following is SQL:

Alter procedure [dbo]. [sp_LoadSellerDataByTime] @ SellerCode NVARCHAR (36), -- merchant code @ StartTime CHAR (20), -- start date @ EndTime CHAR (20), -- end date @ pageSize INT = 31, -- How many pieces of data exist on a page @ pageIndex INT = 1 -- page number of ASDECLARE @ totalCount INT -- number of returned data items -- daily turnover table SELECT * INTO # temp_Count FROM (select left (convert (varchar, table0.CreateTime, 21), 10) AS MonthDate/* date */, SUM (table0.TotalCharge) AS SumMonthMoney/* total turnover today */FROM [CommunityCatering]. [Catering]. [SellerIncome] table0WHERE table0.SellerCode = @ SellerCode/* sellerCode */group by left (convert (varchar, table0.CreateTime, 21), 10 )) AS tempCount -- query the total amount SELECT * INTO # MainTable FROM (SELECT MonthDate, SumMonthMoney, SumTotalMoney, ISNULL (TotalCount, 0) AS TotalCount FROM (SELECT MonthDate, SumMonthMoney, sumTotalMoney = (select sum (SumMonthMoney) FROM # temp_Count t1WHERE t1.MonthDate <= t2.MonthDate) FROM # temp_Count t2) as sileft join (/* query the daily order quantity */select left (convert (varchar, Ord. completeTime, 21), 10) AS MonthDate1, COUNT (1) AS TotalCount/* Order Quantity */FROM CommunityCatering. catering. [Order] Ord WHERE ord. completeTime is not null and ord. validStatus = 5/* validity: select the completed order */AND ord. sellerCode = @ SellerCode/* merchant code */group by left (convert (varchar, Ord. completeTime, 21), 10) AS temp on si. monthDate = temp. monthDate1) AS MainTable SELECT *, ROW_NUMBER () OVER (order by # MainTable. monthDate) AS RowNum/* fields required for paging */FROM # MainTable WHERE # MainTable. monthDate BETWEEN @ StartTime AND @ EndTime/* data from the end time of start time */-- calculate the total number of data entries SELECT @ totalCount = ISNULL (COUNT (1), 0) FROM # MainTable WHERE # MainTable. monthDate BETWEEN @ StartTime AND @ EndTime -- total output data records SELECT @ totalCount AS TotalCount -- delete temporary table drop table # MainTable drop table # temp_Count

Code above:

For example, the first column represents the day, the second column represents the day's turnover, and the third column represents all the turnover before the day. It can be seen that the third column is an accumulation of the second column.

In SQL, a temporary table is defined first, and then a small nested query is used. In this nested query, we calculate the daily turnover accumulation.