查询表的最新记录

时间:2022-11-17 04:33:45


-- 方法1
select a.* 
 from table1 a
 where not exists(select 1 
                  from table1 b
                  where b.name=a.name and b.gdtime>a.gdtime)


-- 方法2
select a.*
 from table1 a
 inner join
 (select name,
         max(gdtime) 'maxgdtime'
  from table1 

  group by name) b on a.name=b.name and a.gdtime=b.maxgdtime


--方法3



/* 1 用子查询 */
select yhh, name, gdcs, gdsj1, gdtime
from table1
where exists
(
    select 1 from
    (
        select yhh, max(gdtime) as gdtime
        from table1 
        group by yhh
    ) x
    where x.yhh = table1.yhh 
      and x.gdtime = table1.gdtime
)


/* 2 用窗口函数 */
select * from
(
select yhh, name, gdcs, gdsj1, gdtime
, max(gdtime) over(partition by yhh) as gdtimeMax
from table1
) x
where gdtime = gdtimeMax