The first column is grouped by Goodsid and then sorted by Audittime in ascending order in the grouped records to show how many times a product has changed.
The second column is to take the last change price of the product Newprice, and then assign the value to the other line of the product, for example, for Goodsid 1, the last Newprice is 20, then for all Goodsid 1 records Curprice are written 20, So as to achieve the effect of outside control distribution.
as follows, the more normal data records:
Special two-column effects need to be added:
The previous practice was to take the normal records out of the C # server (sorted by Goodsid and Audittime first), then iterate through the dataset, add two columns, and solve the Expandfield value problem by first looping, With the key value to the artful record of the last Newprice, the second loop assigns the key value to the curprice of the same goodsid as the record Newprice. The code is as follows:
Copy Code code 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 pairs
Increase distribution Columns
foreach (DataRow row in dt. Rows) {
if (Goodsid!= convert.toint32 (row["Goodsid"])) {
Curpriceobj. ADD ("Goodsid_" +goodsid, Curprice);
Processing
index = 0;
Goodsid = Convert.ToInt32 (row["Goodsid"));
}
Curprice = Convert.todecimal (row["Newprice"));
Index + 1;
row["Expandfield"] = "the first" + Index + "secondary change";
}
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"]];
}
}
But now that the report stored procedures must be paginated, and the pagination to support sorting, if the client wants to sort by the latest price newprice, it is obvious that the data source converted through the server is not supported (because the data source before the database is paginated does not newprice the field).
In addition, the C # service-side processing, in fact, is compared to the memory, its pair of DataTable traversal two times, and also used a temporary key value pairs of objects, repeated assignment is a performance consumption.
To sum up, finally I chose to put the database to build these two columns.
About the first column local sort, I quickly thought of a previously read Grammar row_number () over (PARTITION by ...). Order by), so that the first column of Expandfield is easily built, as SQL follows:
Copy Code code as follows:
SELECT ' first ' + CAST (Row_number () over (PARTITION by Goodsid ORDER by Audittime DESC) as VARCHAR (10)) + ' change ' as Expandfield,
Goodsid, Price, discount, Newprice, begindate as [date]
From #test
But the real trouble is the second column, I dug the skull to think about it, and even thought of using temporary tables to record the result set, and then use the cursor to traverse the result set, update the table to modify the record, but still feel quite cumbersome, and poor performance. Then I consulted the company's DBA, and she gave a way of thinking that the scheme was optimal both in terms of efficiency and feasibility. First post the code as follows:
Copy Code code as follows:
With _temp as (
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 ' first ' + CAST (a.num as VARCHAR (10)) + ' transition ' as Expandfield,
A.goodsid, A.price, A.discount, A.newprice, A.date, B.newprice as Curprice
From _temp A
INNER JOIN _temp b on a.goodsid = B.goodsid and b.num2 = 1
The autocorrelation query, which uses a CTE expression, is highly advantageous in terms of performance, reducing the cost to tempdb relative to the temporary table, and the code is more readable than nested queries, and the NUM column is used to build the first transition column, It's a lot more ingenious here. According to the num2 column of the audittime descending sequence, in the following temporary record B, a record of all GOODSID final price changes is obtained by specifying B.NUM2 = 1, and then a inner join is used to get the value Curprice value we want.
In addition, as there are more than one column on the sorting field, SQL will choose the latter to rearrange, that is, first by Goodsid group, and then in each group by Audittime in ascending order, if the NUM placed in the front, num2 placed in the post, is not the record we want.
