ACCESS 排名取值

ACCESS 沒有ROW_NUMBER()的功能,因此若要排名次則改用如下的方式,

CASE 1: 在相同購買物品項目中,找出最後2次購買的資料:

SELECT X.購買物品, X.第幾次, X.價格, count(Y.購買物品) AS Rank, (X.購買物品 &  '-' & X.第幾次 ) as ID
FROM DataTable AS X, DataTable AS Y
WHERE X.購買物品 = Y.購買物品 and
           (X.第幾次 <  Y.第幾次 or (X.購買物品 = Y.購買物品 and X.第幾次= Y.第幾次  ))
GROUP BY X.購買物品, X.第幾次, X.價格
HAVING count(Y.購買物品) <= 2;

CASE 2: 在相同購買物品項目中,僅保留最後2次購買的資料:

Delete from DataTable where (購買物品 &  '-' & 第幾次 ) not in 
(
SELECT (X.購買物品 &  '-' & X.第幾次 ) as ID
FROM DataTable AS X, DataTable AS Y
WHERE X.購買物品 = Y.購買物品 and
      (X.第幾次 <  Y.第幾次 or (X.購買物品 = Y.購買物品 and X.第幾次= Y.第幾次))
GROUP BY X.購買物品, X.第幾次, X.價格
HAVING count(Y.購買物品) <= 2
)
PS:若是DataTable有主索引,則更方便操作.