查询最后一次消费记录

/*
查询最后一次消费记录
表结构如下
create table TESTSHIC
(
  NAME        VARCHAR2(50),
  CREATE_DATE DATE,
  DAL         NUMBER(10,1)
)
tablespace OMS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );*/

-- row_number() 是进行一个排序 over() 分析函数
-- (partition by t.name ) 对name 进行分组 条件是 日期从大到小
with a as
 (select t.*,
         row_number() over(partition by t.name order by t.create_date desc) rn
    from testshic t)
select *
  from a
 where a.rn = 1

--oracle 写法
with a as (select t.*,
                  row_number()
                  over(partition by t.name order by t.create_date desc) rn
             from testshic t)
select *
  from a
 where a.rn = 1
---------------
--sqlserver 写法
select *
  from testshic t
 where (t.name, t.create_date) in (select t.name, max(t.create_date)
                                     from testshic t
                                    group by t.name)
                                   
       select *
  from testshic t
 where  t.create_date  in (select  max(t.create_date)
                                     from testshic t
                                    group by t.name)