select date,name from( select date,name, row_number()over(partition by name order by date) rn1, row_number()over(partition by name order by date desc) rn2 from table1 )where rn1=1 or rn2=1 order by name,date
查询最早和最晚的记录,使用1楼的方法 直接计算时间差可以直接max-min获得 select name,max(date),min(date),max(date)-min(date) 时间差 from table1 group by name
版主,如果我需要用楼1的rn1-rn2的时间,用二楼的语句应该怎么写啊!
select name,trunc(date) 日期,min(date) 起始时间,max(date) 截止时间,max(date)-min(date) 时间差 from table1 group by name,trunc(date)
from(
select date,name,
row_number()over(partition by name order by date) rn1,
row_number()over(partition by name order by date desc) rn2
from table1
)where rn1=1 or rn2=1
order by name,date
直接计算时间差可以直接max-min获得
select name,max(date),min(date),max(date)-min(date) 时间差
from table1
group by name
from table1
group by name,trunc(date)