SQL Server's clever auto-Association Application

The first column is grouped by goodsid. Then, the records after the group are sorted by audittime in ascending order to display the number of changes of a product.

The second column is to take the final change price newPrice of the product and then assign the value to other rows of the product. For example, if goodsid is 1, The Last newprice is 20, the value of curprice is 20 for all records whose goodsid is 1, so as to achieve the distribution of the control outside.

The following shows a normal data record:

Two special columns need to be added:

Previously, the C # server extracts normal records (sorted by GoodsId and audittime), traverses the entire dataset, and adds two columns, solve the expandfield value problem through the first loop, and skillfully record the last newPrice with key-value pairs. In the second loop, the newPrice of the key-Value Pair record is assigned to the curprice of the same goodsid. The Code is as follows:Copy codeThe Code is as follows: private void ChangeDataTable (DataTable dt)
{
Dt. Columns. Add ("curprice ");
Dt. Columns. Add ("expandfield ");
Int goodsid = 0;
Int index = 1; // pointer
Decimal curprice = 0;
IHashObject curpriceobj = new HashObject (); // key-Value Pair
// Add a distribution Column
Foreach (DataRow row in dt. Rows ){
If (goodsid! = Convert. ToInt32 (row ["goodsid"]) {
Curpriceobj. Add ("goodsid _" + goodsid, curprice );
// Process
Index = 0;
Goodsid = Convert. ToInt32 (row ["goodsid"]);
}
Curprice = Convert. ToDecimal (row ["newprice"]);
Index + = 1;
Row ["expandfield"] = "" + index + "";
}
If (dt. Rows. Count! = 0) {// Add the current price of the last item
Curpriceobj. Add ("goodsid _" + goodsid, curprice );
}
// Increase the current price
Foreach (DataRow row in dt. Rows ){
Row ["curprice"] = curpriceobj ["goodsid _" + row ["goodsid"];
}
}

However, the report stored procedure must be paginated and sorted after pagination. If the client needs to be sorted by the newest price newPrice, obviously, the data source converted through the server cannot be supported (because the data source before the database page does not have the newPrice field ).
In addition, the processing on the C # server is actually memory-consuming. A pair of datatables is traversed twice, and temporary key-value pairs are also used for objects. Repeated value assignment is a performance consumption.
To sum up, I finally chose to put the two columns in the database.
Concerning the partial sorting of the first column, I soon came up with a previously read syntax ROW_NUMBER () OVER (PARTITION... order by), so the first column expandfield is easily built. The SQL statement is as follows:Copy codeThe Code is AS follows: SELECT 'nth '+ CAST (ROW_NUMBER () OVER (partition by GoodsId order by audittime DESC) as varchar (10) + 'change once' AS expandfield,
GoodsId, price, discount, newPrice, begindate as [date]
FROM # test

But what is really troublesome is the second column. I don't even think about it. I even want to use a temporary table to record this result set, and then use a cursor to traverse the result set, update this table to modify records, but it still feels quite complicated and has poor performance. Later, I consulted the DBA of the company. She gave an idea that this solution is optimal in terms of efficiency and feasibility. First, paste the Code as follows:Copy codeThe Code is AS follows: WITH _ temp (
SELECT ROW_NUMBER () OVER (partition by GoodsId order by audittime DESC) AS num2,
ROW_NUMBER () OVER (partition by GoodsId order by audittime) AS num,
GoodsId, price, discount, newPrice, begindate as [date]
FROM # test
)
SELECT 'nth '+ CAST (a. num as varchar (10) + 'change' AS expandfield,
A. goodsid, a. price, a. discount, a. newprice, a. date, B. newprice AS curprice
FROM _ TEMP
Inner join _ TEMP B ON a. GoodsId = B. GoodsId AND B. num2 = 1

Here we use a CTE expression for self-join queries, which is advantageous in terms of performance. Compared with temporary tables, it reduces the overhead of tempdb. In addition, the Code is more readable than nested queries, the num column is used to construct the nth change column. Here, it is more clever to add the num2 column according to the audittime downsequence. In the temporary record B below, B is specified. num2 = 1 get all records of the final price change of goodsid, and then use an inner join to get the expected value curprice.

In addition, if there are sorting fields in multiple columns above, SQL will sort the fields by goodsid and sort them by audittime in ascending order, if we put num before and num2 after, we will not be able to get the record we want.