分组查询最晚一条数据（ORACLE）

现有客户表，交费表，需查询每个存在交费记录客户的最后一笔交费信息

这里提供两种方式

注：客户不会在同一时间有两条交费，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) btime
     FROM 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