SQLServer 2008 R2中使用Cross apply统计最新数据和最近数据（sqlserver2008介绍）这都可以


–by wls
—
–网络代码有风险
–复制粘贴须谨慎
————————————————————————
–你可以尝试一下这个，看看是什么结果。
–产生这种原因是因为没有做出具体规定。
————————————————————————
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
INSERT INTO t_TestbyWLS VALUES
(‘A’,,”,.)
,(‘B’,,”,.)
,(‘B’,,”,.)
,(‘A’,,”,.)
,(‘B’,,”,.)
,(‘A’,,”,.)
,(‘A’,,”,.)
GO
WITH TempChkDate
AS
(
SELECT PName,PSId,ChkDate,Price,DENSE_RANK() OVER(PARTITION BY PName ORDER BY CAST(Chkdate AS INTEGER) DESC ) AS DRID,
ROW_NUMBER() OVER(PARTITION BY 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