SQL Server 2008 R2 uses cross apply to statistic the latest and most recent data _mssql2008

Use the APPLY operator to call a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function is the right input, and the outer table expression is left-input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the set of columns in the left input, followed by the list of columns returned by the right input.

Note: To use apply, the database compatibility level must be at least 90.

There are two forms of apply: CROSS apply and OUTER apply. CROSS APPLY returns only the rows in the external table that generate the result set through table-valued functions. OUTER APPLY returns both the row that generated the result set and the row that does not generate the result set, where the value in the column generated by the table-valued function is NULL.

Haven't written SQL for a long time, the hands are a little raw. Haha, answer a question today. By the way, record them.

The needs of the victim

The problem for the victim should be to put the latest data and new data on a single line.

Because there is no indication of how the duplication is handled, that is, there are several new data or multiple secondary data, so I did not do too much processing.

--by WLS----Network code is risky--copy and paste should be careful to use the tempdb go IF object_id (' t_testbywls ', ' U ') are not NULL DROP TABLE t_testbywls Go creat E TABLE t_testbywls (pname NVARCHAR (), Psid integer,chkdate NVARCHAR (), price FLOAT) go inserts into t_testbywls VALUES (' A ',
'',.)
, (' B ',,, ',.)
--, (' B ',,,,,.)
, (' A ',,, ',.)
, (' B ',,, ',.)
, (' A ',,, ',.)
--, (' A ',,, ',.) Go select * from t_testbywls go/* Select Pname,psid,chkdate,price,dense_rank () over (PARTITION by pname Order by CAST (CHKD Ate as Integer) DESC as Drid, Row_number () over (PARTITION by pname Order by CAST (chkdate as Integer) Desc,price DESC) S RID from t_testbywls go/with Tempchkdate as (SELECT Pname,psid,chkdate,price,dense_rank ()-PARTITION by PName OR DER by cast (chkdate as Integer) DESC) as Drid, Row_number () over (PARTITION to PName order by cast (chkdate as Integer) DES C,price DESC) as RID from t_testbywls) SELECT TCD. Pname,tcd. Psid,tcd. Chkdate,tcd. Price,/*tcd. Drid,tcd. RID,*/T.TCD,T.TP from Tempchkdate as TCD CROSS APPLY (SELECT chkdate as TCD,Price as TP from Tempchkdate WHERE--TCD. Drid= and TCD. Pname=tempchkdate.pname and TCD. Psid=tempchkdate.psid and tempchkdate.drid=) as T WHERE TCD.  Drid= Go

The results of the run should be correct.

But it's not very good to see the execution plan.

You can change it when you are free.

You can try this and see what happens.

The reason for this is that you have not made a specific provision.

--by WLS----Network code is risky--copy and paste should be cautious--------------------------------------------------------------------------you can try this.
And see what the results are.
-This is because no specific provision has been made. ------------------------------------------------------------------------use tempdb go IF object_id (' t_testbywls ', ' U ') is not NULL DROP TABLE t_testbywls go CREATE table t_testbywls (PName NVARCHAR (), Psid integer,chkdate NVARCHAR (), Price FLOAT) go inserts into t_testbywls VALUES (' A ',,, ',.), (' B ',,, ' ',.], (' B ',,, ',.), (' A ',,, ',.), (' B ',,,,,.), (' A ',,, ',.), (' A
',,'',.) Go with Tempchkdate as (SELECT Pname,psid,chkdate,price,dense_rank ()-PARTITION by PName ORDER by CAST (Chkdate as INT  EGER) DESC) as Drid, Row_number () over (PARTITION to PName ORDER by CAST (chkdate as INTEGER) Desc,price DESC) as RID from t_testbywls) SELECT TCD. Pname,tcd. Psid,tcd. Chkdate,tcd. Price,tcd. Drid,tcd. RID,T.TCD,T.TP from Tempchkdate as TCD CROSS APPLY (SELECT chkdate as TCD, price as TP from Tempchkdate WHERE--TCD. Drid= and TCD. Pname=tempchkdate.pname and TCD.Psid=tempchkdate.psid and tempchkdate.drid=) as T WHERE TCD. Drid= Go

The above content is small to introduce the SQL Server 2008 R2 Use cross apply statistics the latest data and recent data related knowledge, hope to help everyone!