SQL Server 2008 R2 使用 CROSS APPLY 取得最新與次新資料

APPLY 運算子簡介

• CROSS APPLY：僅返回外部表經表值函數產生結果集的行（內連接語意）
• OUTER APPLY：返回所有外部表行，無結果集者函數欄位為 NULL（外連接語意）
• 需資料庫相容性等級 ≥ 90

需求場景

依 PName、PSId 分組，取得每組最新（最新檢查日期）與次新資料同列顯示。

完整測試腳本

USE tempdb;
IF OBJECT_ID('t_TestbyWLS','U') IS NOT NULL DROP TABLE t_TestbyWLS;
CREATE TABLE t_TestbyWLS(PName NVARCHAR(50), PSId INT, ChkDate NVARCHAR(20), Price FLOAT);
INSERT INTO t_TestbyWLS VALUES
('A',1,'20161201',10.5), ('B',1,'20161201',20.3),
('B',1,'20161202',21.0), ('A',1,'20161202',11.2),
('B',1,'20161203',22.5), ('A',1,'20161203',12.8),
('A',1,'20161204',13.1);

WITH TempChkDate AS (
  SELECT PName, PSId, ChkDate, Price,
         DENSE_RANK() OVER(PARTITION BY PName ORDER BY CAST(ChkDate AS INT) DESC) AS DRID,
         ROW_NUMBER() OVER(PARTITION BY PName ORDER BY CAST(ChkDate AS INT) DESC, Price DESC) AS RID
  FROM t_TestbyWLS
)
SELECT tcd.PName, tcd.PSId, tcd.ChkDate, tcd.Price,
       t.ChkDate AS PrevChkDate, t.Price AS PrevPrice
FROM TempChkDate tcd
CROSS APPLY (
  SELECT ChkDate, Price FROM TempChkDate
  WHERE PName = tcd.PName AND PSId = tcd.PSId AND DRID = 2
) t
WHERE tcd.DRID = 1;

執行計畫分析

當前寫法掃描兩次 CTE，大數據量下效能一般，後續可優化為單次掃描或使用 LAG/LEAD 窗口函數（SQL Server 2012+）。

補充：重複資料處理示範

INSERT INTO t_TestbyWLS VALUES
('A',1,'20161201',10.5), ('B',1,'20161201',20.3),
('B',1,'20161201',20.3), ('A',1,'20161202',11.2),
('B',1,'20161202',21.0), ('A',1,'20161203',12.8),
('A',1,'20161203',12.8);

執行同樣查詢觀察 DRID、RID 變化，依業務規則決定去重邏輯。