MySql 的 Except - 查詢最近第二次交易以文找文

[yehlu]

http://blog.yam.com/ymwen/article/20556456

今天遇到了一個需求是，希望列出會員交易記錄的最近兩次交易日期。其實最直覺的作法是將所有的交易 records 叫出來後，透過程式來分析資料；又或者是找出所有的會員編號，再透過會員編號一個個去取最近的兩筆交易記錄。


第一個方法其實是 ok 的，反正現在 server 硬體都不賴，浪費點資源比對一下資料也就完成了；不過第二個方法就不建議了，理由是 Queries 太多，如果有 100 個會員就要 query 一百次，這樣會很慢。於是想到，如果只透過下一個 SQL query，是否有辦法找到最近第二次的交易日期？


最後想到了 Except，中文應該叫差集，概念是先將全部的交易日期去扣掉最近一次的交易日期，再從剩下的集合裡去找出最近的。不過 mySql 沒有差集，網路上討論到了用 where not exists 或 left join ... where table2.id is null 來實現 except，最後我採用了 left join。以下是 query 寫法：


假設 table name 是 test, 會員 aid, 交易日是 date。


最近一筆的 query：select t3.aid, max(t3.date) as date from test as t3 group by t3.aid
全部的 query：select t2.aid, t2.date from test as t2


以 t2 為主作 Except：
select t2.aid, t2.date from test as t2
left join
(select t3.aid, max(t3.date) as date from test as t3 group by t3.aid)
as t4 ON t2.aid=t4.aid where t2.date!=t4.date


最後把 Except 的結果取最近一筆，即為全部的最近第二筆：
select t1.aid, max(t1.date) as last2date from
(select t2.aid, t2.date from test as t2
left join
(select t3.aid, max(t3.date) as date from test as t3 group by t3.aid)
as t4 ON t2.aid=t4.aid where t2.date!=t4.date) as t1
group by t1.aid