SQL求当天第一条与最后一条之差

[bld213]

test表 用SQL语句实现
id name rq sj
1 张三 2009-09-14 08:08:10 10
2 张三 2009-09-14 08:10:10 20
3 张三 2009-09-14 08:20:10 30
4 李四 2009-09-14 08:08:10 10
5 李四 2009-09-14 08:10:10 40
6 李四 2009-09-14 08:22:10 50
7 李四 2009-09-14 09:22:10 80
求结果 不同名称 日期最后一条减第一条的数据
name rq sj
张三 2009-09-14 20 是（第3条-第1条）
李四 2009-09-14 70 是（第7条-第4条）

求简洁的，我分别找出最大最小，然后连接进行相减的

[yaiger]

引用 3 楼 qq646748739 的回复:

上述SQL需要改下：

select name, rq, max_sj - min_sj as sj
  from (select name,
               trunc(rq) as rq,
               first_value(sj) over(partition by name order by rq) as min_sj,
               first_value(sj) over(partition by name order by rq desc) as max_sj
          from test);

你是把partition by里面的trunc(rq)删除了吧. 如果数据中包含了多天的数据, 还是应该加上trunc(rq)

[可乐乐可]

select t2.name,
       t2.rq,
       (select tmax.sj from test tmax
         where tmax.name = t2.name
           and tmax.rq = t2.max_time) -
       (select tmin.sj from test tmin
         where tmin.name = t2.name
           and tmin.rq = t2.min_time)
  from (select t.name,
               trunc(t.rq) rq,
               max(t.rq) max_time,
               min(t.rq) min_time
          from test t
         group by t.name, trunc(t.rq)) t2

[可乐乐可]

或者

select distinct(name), rq, max_sj - min_sj as sj
  from (select name,
               trunc(rq) as rq,
               first_value(sj) over(partition by name order by rq) as min_sj,
               first_value(sj) over(partition by name order by rq desc) as max_sj
          from test_wx);  

[可乐乐可]

上条的表名需要修改成test

[bld213]

帮助一下呗，谢谢了！

[yaiger]

随便写的，没有测试，你自己测一下吧

select name, rq, max_sj - min_sj as sj
from (select name,
trunc(rq) as rq,
first_value(sj) over(partition by name, trunc(rq) order by rq) as min_sj,
first_value(sj) over(partition by name, trunc(rq) order by rq desc) as max_sj
from test);

[碧水幽幽泉]

上述SQL需要改下：

select name, rq, max_sj - min_sj as sj
  from (select name,
               trunc(rq) as rq,
               first_value(sj) over(partition by name order by rq) as min_sj,
               first_value(sj) over(partition by name order by rq desc) as max_sj
          from test);

[碧水幽幽泉]

也可以用：

select name, rq, max_sj - min_sj as sj
  from (select name,
               trunc(rq) as rq,
               first_value(sj) over(partition by name order by rq) as min_sj,
               last_value(sj) over(partition by name order by rq ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as max_sj
          from test
	  );

rq如果是字符串型，trunc(rq) 则需要改成substr(rq,1,10)

[Nani_Tgc]

trunc()是什么方法