时间戳

首页 > 解决方案 > 如何根据日期查找每个 id 的最后一个复合键

sql - 如何根据日期查找每个 id 的最后一个复合键

问题描述

我正在使用 Oracle 开发人员，

并希望为我的表“bookborrow”找到每个 id 的最后一条记录，它有composite key，id并且dateofborrow是主键（复合键）

列 ：

id  |  studname  |  dateofborrow  |  bookname    
----+------------+----------------+------------------------
1   |  *Scotty*  |  10-OCT-05     |  DB Admin      
1   |  *Scotty*  |  16-NOV-05     |  Database Security    
1   |  *Scotty*  |  09-DEC-06     |  sql server 2005   
2   |  *Andy*    |  30-MAR-04     |  Math  
2   |  *Andy*    |  14-JUN-06     |  Calculation of matrix   
3   |  *Jack*    |  29-JAN-09     |  physics

如何根据这样的dateofborrow输出找到每个 id 的最后一条记录？

id  |  studname  |  dateofborrow  |  bookname    
----+------------+----------------+------------------------
1   |  Scotty    |  09-DEC-06     |  sql server 2005     
2   |  Andy      |  14-JUN-06     |  Calculation of matrix     
3   |  Jack      |  29-JAN-09     |  physics

有什么帮助吗？

标签： sqloracle

解决方案

你需要什么可以通过太多方式实现，这里有两个

with my_data_set as (
            select 1 as id,'Scotty' as studname , '10-OCT-05' as dateofborrow,'DB Admin' as bookname from dual union
            select 1 as id,'Scotty' as studname , '16-NOV-05' as dateofborrow,'Database Security' as bookname from dual union
            select 1 as id,'Scotty' as studname , '09-DEC-06' as dateofborrow,'sql server 2005' as bookname from dual union
            select 2 as id,'Andy' as studname , '30-MAR-04' as dateofborrow,'Math' as bookname from dual union
            select 2 as id,'Andy' as studname , '14-JUN-06' as dateofborrow,'Calculation of matrix' as bookname from dual union
            select 3 as id,'Jack' as studname , '29-JAN-09' as dateofborrow,'physics' as bookname from dual
           ) 
           select * from my_data_set a
           where a.dateofborrow = (select max(to_date(dateofborrow,'DD-MONTH-YY')) from my_data_set where a.id = id);


with my_data_set as (
            select 1 as id,'Scotty' as studname , '10-OCT-05' as dateofborrow,'DB Admin' as bookname from dual union
            select 1 as id,'Scotty' as studname , '16-NOV-05' as dateofborrow,'Database Security' as bookname from dual union
            select 1 as id,'Scotty' as studname , '09-DEC-06' as dateofborrow,'sql server 2005' as bookname from dual union
            select 2 as id,'Andy' as studname , '30-MAR-04' as dateofborrow,'Math' as bookname from dual union
            select 2 as id,'Andy' as studname , '14-JUN-06' as dateofborrow,'Calculation of matrix' as bookname from dual union
            select 3 as id,'Jack' as studname , '29-JAN-09' as dateofborrow,'physics' as bookname from dual
           ) 
           select * from (
           select a.*,row_number() over (partition by id order by to_date(dateofborrow,'DD-MONTH-YY') desc) r  from my_data_set a
           ) where r =1;

推荐阅读