objectid car_no sendtime
1 9507 2009-7-25 10:59:30
2 9507 2009-7-24 11:05:30
3 9507 2009-7-24 11:29:50
4 9507 2009-7-24 11:22:30
5 9508 2009-7-24 11:22:30
6 9508 2009-7-24 11:29:45
7 9508 2009-7-24 11:29:50
8 9508 2009-7-24 11:29:48
9 9508 2009-7-24 11:26:30先要分别查出9507和9508两个车辆的最大的sendtime,返回如下
objectid car_no sendtime3 9507 2009-7-24 11:29:50
7 9508 2009-7-24 11:29:50请问sql语句该如何写
1 9507 2009-7-25 10:59:30
2 9507 2009-7-24 11:05:30
3 9507 2009-7-24 11:29:50
4 9507 2009-7-24 11:22:30
5 9508 2009-7-24 11:22:30
6 9508 2009-7-24 11:29:45
7 9508 2009-7-24 11:29:50
8 9508 2009-7-24 11:29:48
9 9508 2009-7-24 11:26:30先要分别查出9507和9508两个车辆的最大的sendtime,返回如下
objectid car_no sendtime3 9507 2009-7-24 11:29:50
7 9508 2009-7-24 11:29:50请问sql语句该如何写
objectid car_no sendtime
1 9507 2009-7-25 10:59:30
2 9507 2009-7-24 11:05:30
3 9507 2009-7-24 10:29:50
4 9507 2009-7-24 11:22:30
5 9508 2009-7-24 11:22:30
6 9508 2009-7-24 11:29:45
7 9508 2009-7-24 11:29:50
8 9508 2009-7-24 11:29:48
9 9508 2009-7-24 11:26:30 先要分别查出9507和9508两个车辆的最大的sendtime,返回如下
objectid car_no sendtime 4 9507 2009-7-24 11:22:30
7 9508 2009-7-24 11:29:50 请问sql语句该如何写
表
where t.car_no in ('9507','9508')
group by t.objectid,car_no
from (select objectid,car_no,send_time, row_number() over(partition by car_no order by sendtime desc) timeorder
from tablename) t
where t.timeorder = 1;
from
(
select objectid,car_no,sendtime,
row_number() over(partition by car_no order by sendtime desc) rn
from test
)
where rn=1;
你这里的数据,car_no为9507的第一条数据,时间是25号的,肯定是最大的,所以你要的结果集就有点问题
from (select t.*,
row_number() over(partition by t.car_no order by t.sendtime desc) rn
from test_b t)
where rn = 1查询数据和你给的数据有出入,请确认你需要的数据
不用统计的话select * from test a where exists (select 1 from (select max(sendtime) from test where car_no in ('9507','9508') group by car_no) b where a.card_no = b.card_no and a.sendtime=b.sendtime);
如果楼主只需要查出其中一条数据,那么应该把objectid这一列从查询结果中去掉
如果需要把他们都列出来,则分析函数应该改为rank()
select *
from (select t.*,
rank() over(partition by t.car_no order by t.sendtime desc) rk
from test_b t)
where rk = 1
row_number()是个函数,得到行号,如果后面那串不要,就是对所有记录编号
over(Partition by t.car_no order by t.sendtime desc)
这个是表示对car_no分组,按sendtime排序
和row_number()一起使用变成了按car_no分组,sendtime排序生成一个序号