现有这样一个场景,一个表中有姓名、性别、年龄、交易时间、产品字段。需求是取出每个人最近购买的一笔产品的信息,即是每个人购买每一种不同产品的最新的一笔数据的信息,而且包含人员其它相关信息。 --mysql建表 create table t_trade ( name varchar(10), sex varchar(2), age int(10), datadate varchar(10), product varchar(20) ); --orcle建表 create table t_trade ( name varchar(10), sex varchar(2), age number, datadate varchar(10), product varchar(20) ) Mysql中可以通过设变量的方式来解决 select * from ( select t1.name, t1.age, t1,datadate, t1.product, if(@pd = t1.name and @pf = t1.product, @rank:=@rank + 1, @rank:=1) as rn, @pd:= t1.name, @pf:=t1.product from( select name, age, datadate, product from t_trade order by name, product, datadate desc ) t1 ) t2 where t2.rn = 1; Oracle中可以通过 over(partition by … order by …)函数进行处理 select * from ( select t1.*, row number() over (partition by name, product order by datadate desc) rn from (select name, age, datadate, products from t_trade) t1 ) t2 where t2.rn = 1;