获取分组后取某字段最大一条记录（求每个类别中最大的值的列表）

获取分组后取某字段最大一条记录
方法一：(效率最高)
select a.* from test a, (select pid, max(version) version from test group by pid) b 
where a.pid= b.pid and a.version = b.version order by a.pid

方法二（同一）：
select a.* from test a inner join (select pid , max(version) version from test group by pid) b on a.pid = b.pid and a.version = b.version order by a.pid

方法三：(效率次之，当一个组中version最大值不唯一，只查其中一个)
select * from
(
select *, ROW_NUMBER() OVER(PARTITION BY pid order by version desc) as num
from test
) t
where t.num = 1

方法四：(效率最低, 此方式select pid 为dependent subquery，子查询依赖外部查询)
select * from test as a 
where version = (select max(b.version) 
from test as b 
where a.pid = b.pid );