現有客戶表，交費表，需查詢每個存在交費記錄客戶的最后一筆交費信息

這里提供兩種方式

注：客戶不會在同一時間有兩條交費，SQL可直接執行

--查詢客戶名稱，最后一筆交費時間，以及最后一筆交費金額
WITH
--客戶表：客戶id，客戶名稱
a AS(
SELECT 'a1' aid, '張三' aname FROM dual
UNION ALL 
SELECT 'a2', '李四' FROM dual
UNION ALL 
SELECT 'a3', '王五' FROM dual
),
--交費表：交費ID，客戶ID，交費時間（以數字代替，數字越大時間越晚），交費金額
b AS(
SELECT 'b1' bid,'a1' aid, '1' btime, 10 bnum FROM dual
UNION ALL 
SELECT 'b2','a1', '2',30 FROM dual
UNION ALL 
SELECT 'b3', 'a1', '3',40 FROM dual
UNION ALL 
SELECT 'b4', 'a2', '1',40 FROM dual
UNION ALL 
SELECT 'b5', 'a2', '3',60 FROM dual
UNION ALL 
SELECT 'b6', 'a3', '1',50 FROM dual
)
--方式一：子查詢
/*SELECT a.aname,b1.bnum,b1.btime
FROM b b1
JOIN (SELECT b.aid,MAX(b.btime) btimeFROM b GROUP BY b.aid) b2 ON b2.aid = b1.aid AND b2.btime = b1.btime
JOIN a ON b1.aid = a.aid
ORDER BY 1,2,3
*/
--方式二：分析函數
/*SELECT aname,bnum,btime
FROM (
SELECT a.aname,b.bnum,b.btime,
row_number() OVER (partition by b.aid order by b.btime desc) rn
FROM b
JOIN a ON a.aid = b.aid
)
WHERE rn = 1
ORDER BY 1,2,3
--方式三：NOT EXISTS
SELECT a.aname,b.bnum,b.btime 
FROM a
JOIN b ON b.aid = a.aid
AND NOT EXISTS(
SELECT 1 FROM b b1
WHERE b1.aid = b.aid
AND b1.btime > b.btime)
ORDER BY 1,2,3*/
--方式四：開窗函數
SELECT DISTINCT a.aname,
first_value(b.bnum) OVER (partition by b.aid order by b.btime DESC) bnum,
last_value(b.btime) OVER (partition by b.aid) btime
FROM b
JOIN a ON a.aid = b.aid
ORDER BY 1,2,3

查詢結果如下

first_value和last_value用法不同可以參照

Oracle分析函數之FIRST_VALUE和LAST_VALUE