To improve the performance, you can create two indexes on the User_id and Date_time separately.select max(date_time) from user where user_id=100;
select * from user where user_id='1003' and date_time=(select max(date_time) from user)
或者:select * from user where user_id='1003' and rownum=1 order by date_time desc;
select * from user where user_id='1003' and date_time=(select max(date_time) from user where user_id='1003')
我的语句是 select * from ( select * from user where user_id=1003 order by date_time desc ) where runmun=1;select * from user where user_id=1003 and date_time=(select date_time from user where user_id=1003); 虽然单条语句不是很慢,但把这个做到触发器里面后,前台应用变得有点慢,虽然能够接受,但我还是想优化一下,索引已建
第一,分开建立索引 二,用select * from user where user_id='1003' and date_time=(select max(date_time) from user) 会快一点
一瓢兄:如果表中有两个值相同而且同为最大,这样就都显示出来了 select * from user where user_id='1003' and date_time=(select max(date_time) from user) and rownum =1;
primary key只可以有一个吧~
date_time=(select max(date_time) from user) 从一个大表里面找一个最大时间为什么会快一点,再说它们不一定相等,子查询要加 where user_id=1003吗
select top 1 * from user where user_id='1003' order by date_time同时要建立user_id 和date-time 的索引!应该会快吧
对,还要加上user_id=100的条件 使用date_time=(select max(date_time) from user)应该会快一点 因为数据范围缩小了、省去了排序楼上是sql server版的吧,呵呵 oracle没有top n的用法
create index name_idx on user(user_id,date_time desc);
where user_id=100;
select * from (
select * from user where user_id=1003 order by date_time desc
) where runmun=1;select * from user where user_id=1003 and date_time=(select date_time from user where user_id=1003);
虽然单条语句不是很慢,但把这个做到触发器里面后,前台应用变得有点慢,虽然能够接受,但我还是想优化一下,索引已建
二,用select * from user where user_id='1003'
and date_time=(select max(date_time) from user)
会快一点
select * from user where user_id='1003'
and date_time=(select max(date_time) from user) and rownum =1;
从一个大表里面找一个最大时间为什么会快一点,再说它们不一定相等,子查询要加
where user_id=1003吗
使用date_time=(select max(date_time) from user)应该会快一点
因为数据范围缩小了、省去了排序楼上是sql server版的吧,呵呵
oracle没有top n的用法