一个表tmp2
gh bm rq
1 3 2002-08-09 00:00:00.000
3 1 2002-09-01 00:00:00.000
4 4 2002-07-01 00:00:00.000
1 4 2002-07-05 00:00:00.000
3 2 2002-08-09 00:00:00.000
想要结果：
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
我现在用的是：
select a.* from tmp2 a join (select gh,max(rq) rq from tmp2 where month(rq)<=8 group by gh) b
on a.gh=b.gh and a.rq=b.rq
还可不可以简化？
---------------------------------------------------------------
Select Max(gh) as gh, bm, rq
From tmp2
Where Month(rq)<=8
Group By bm, rq
试试看
---------------------------------------------------------------
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')
---------------------------------------------------------------
为什么不能找出所有小于8月的数据后，按日期反向排序，然后找到第一笔记录？
select * from tmp2 a
where rq<'2002-8-1'
and rownum = 1
order by rq desc
我没实验，如果ORACLE是先取结果集再排序这个想法就错了，但大家可以按照我这个思路想想，以前用SQL SERVER的TOP 与 ORDER BY 组合就能产生这样的效果
---------------------------------------------------------------
为什嬷不以gh分组，来查分组里最大的日期。
select gh,max(rq) rq from tmp2 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