month(rq)<=8的用法跨年度是不行的:select * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
要稳妥的话:select * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') and bm=(select max(bm) from tmp2 where gh=a.gh and rq=a.rq)
现在没测试环境,中午回家再试。 应该用 select * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') 就可以了,select * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') and bm=(select max(bm) from tmp2 where gh=a.gh and rq=a.rq) 是不是考虑有相同的rq最大值的情况,这个时候就取相同rq中bm大的值? 这种情况倒不会存在。inalover(奇遇) : 这会瞧见了更好的方法了吧,5点多才说去睡,历害^_^
为什么不能找出所有小于8月的数据后,按日期反向排序,然后找到第一笔记录?select * from tmp2 a where rq<'2002-8-1' and rownum = 1 order by rq desc 我没实验,如果ORACLE是先取结果集再排序这个想法就错了,但大家可以按照我这个思路想想,以前用SQL SERVER的TOP 与 ORDER BY 组合就能产生这样的效果
select * from (select * from tmp2 a where rq<'2002-8-1' order by rq desc) tablea where and rownum = 1 刚才的想法的确有点错误,看来ORACLE是先做ROWNUM=1然后在ORDER BY 的 现在改成了从一个子查询中再查询,这样就对了,速度应该不错
为什嬷不以gh分组,来查分组里最大的日期。 select gh,max(rq) rq from tmp2 where rq<'2002-9-1' group by gh order by gh desc
select gh, (select bm from tmp2 where gh=a.gh and rq=a.rq) as bm, max(rq) as rq from tmp2 a where rq<'2002-9-1' group by gh order by gh desc
上面的WHERE后RQ判断有问题select distinct * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh) order by gh desc
扬兄的测试通过 不过应该改成 select * from tmp2 a where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') 也可以吧,因为在这个rq=()里就规定下要小于'2002-9-1',不知道我考虑的周不周全,我拿测试数据试是没问题 pledde() 的不合题意,没有BM字段 OpenVMS(半知半解)的结果不对,你的结果为: 4 4 2002-07-01 00:00:00.000 1 3 2002-08-09 00:00:00.000而我要结果为: 4 4 2002-07-01 00:00:00.000 3 2 2002-08-09 00:00:00.000 1 3 2002-08-09 00:00:00.000要改为: select distinct * from tmp2 a where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') order by gh desc 就可以了,不过其实distinct没什么意义,因为没有两条完全相同的记录 呵呵,谢谢各位,又学了不少,结帐
从效率考虑 select * from tmp2 a where rq<'2002-9-1' and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') 比 select * from tmp2 a where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1') 快
gh bm rq
4 4 2002-07-01 00:00:00.000
3 2 2002-08-09 00:00:00.000
1 3 2002-08-09 00:00:00.000也就是说找出8月以前的rq最大的gh你要的结果是不是有问题啊
From tmp2
Where Month(rq)<=8
Group By rq呵呵,可能又有问题~~
呵呵,结果没什么问题吧,
表达能力欠佳:)就是说,想找出8月份以前的记录,而在这些记录中,如果有相同的gh,就取rq值大的那条记录,所在,在找出来的记录中,gh是唯一的
CoolSlob(努力学习 工作 准备中):
这个肯定也不行啦,bm绝对不可以sum的:)
如果在XXXX情况下应该可以~
4 4 2002-07-01 00:00:00.000
1 4 2002-07-05 00:00:00.000
3 5 2002-08-09 00:00:00.000因为有可能在gh不同的情况rq相同,那么你只按rq分组,就把rq相同的
1 3 2002-08-09 00:00:00.000
3 2 2002-08-09 00:00:00.000
累加了,得出了一条
3 5 2002-08-09 00:00:00.000^_^
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
and bm=(select max(bm) from tmp2 where gh=a.gh and rq=a.rq)
应该用
select * from tmp2 a
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
就可以了,select * from tmp2 a
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
and bm=(select max(bm) from tmp2 where gh=a.gh and rq=a.rq)
是不是考虑有相同的rq最大值的情况,这个时候就取相同rq中bm大的值?
这种情况倒不会存在。inalover(奇遇) :
这会瞧见了更好的方法了吧,5点多才说去睡,历害^_^
where rq<'2002-8-1'
and rownum = 1
order by rq desc
我没实验,如果ORACLE是先取结果集再排序这个想法就错了,但大家可以按照我这个思路想想,以前用SQL SERVER的TOP 与 ORDER BY 组合就能产生这样的效果
(select * from tmp2 a
where rq<'2002-8-1'
order by rq desc) tablea
where and rownum = 1
刚才的想法的确有点错误,看来ORACLE是先做ROWNUM=1然后在ORDER BY 的
现在改成了从一个子查询中再查询,这样就对了,速度应该不错
select gh,max(rq) rq from tmp2 where rq<'2002-9-1'
group by gh order by gh desc
(select bm from tmp2 where gh=a.gh and rq=a.rq) as bm,
max(rq) as rq
from tmp2 a where rq<'2002-9-1' group by gh order by gh desc
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh)
order by gh desc
不过应该改成
select * from tmp2 a
where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
也可以吧,因为在这个rq=()里就规定下要小于'2002-9-1',不知道我考虑的周不周全,我拿测试数据试是没问题
pledde() 的不合题意,没有BM字段
OpenVMS(半知半解)的结果不对,你的结果为:
4 4 2002-07-01 00:00:00.000
1 3 2002-08-09 00:00:00.000而我要结果为:
4 4 2002-07-01 00:00:00.000
3 2 2002-08-09 00:00:00.000
1 3 2002-08-09 00:00:00.000要改为:
select distinct * from tmp2 a
where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
order by gh desc
就可以了,不过其实distinct没什么意义,因为没有两条完全相同的记录
呵呵,谢谢各位,又学了不少,结帐
select * from tmp2 a
where rq<'2002-9-1'
and rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
比
select * from tmp2 a
where rq=(select max(rq) from tmp2 where gh=a.gh and rq<'2002-9-1')
快