sql分组后取第一条数据

应用: 数据库表中有一些数据,例如:设备操作历史记录。每一个设备的每一次操作都存入历史表中,现在想从表里面取出每个设备最近一次的操作记录。

字段:id_k, equid,sys_time。id_k是表的主键,equid是外键引用的设备表的主键,sys_time是操作时间。
分组:
select id_k,equid,to_char(sys_time,’yyyy-MM-dd HH:mi:ss’) ,
row_number() over(partition by equid order by sys_time desc) inn
from eqt_t_history
使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
然后从分组中取第一个记录:
select * from (
select id_k,equid,to_char(sys_time,’yyyy-MM-dd HH:mi:ss’) , 
row_number() over(partition by equid order by sys_time desc) inn
from eqt_t_history
) t where t.inn=1;
从分组中取序号inn为1的记录。因为分组内已经根据时间逆序排列,取inn为1的就是最近一次的操作记录。
This entry was posted in Computer, Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s