select * from A
where exists( select 1 from (
SELECT B.Date FROM B WHERE B.id=A.id order by B.Date desc
)B where rownum<=1 AND B.Date between sysdate-100 and sysdate)
报错啊。a.id 无效, 原因是a.id不能在第二层子查询中
我想实现的效果是 在B表中筛选出和A表同ID的记录 然后对 日期进行排序,排序后取出第一条 然后看第一条的日期是否在指定日期范围
where exists( select 1 from (
SELECT B.Date FROM B WHERE B.id=A.id order by B.Date desc
)B where rownum<=1 AND B.Date between sysdate-100 and sysdate)
报错啊。a.id 无效, 原因是a.id不能在第二层子查询中
我想实现的效果是 在B表中筛选出和A表同ID的记录 然后对 日期进行排序,排序后取出第一条 然后看第一条的日期是否在指定日期范围
where id in (
-- 再嵌套一层
select id from
(
select row_number() over(order by B.Date desc) rn
id,date
from B
where B.Date between sysdate-100 and sysdate
)
where rn = 1
)
select * from A
where exists( select 1 from
(SELECT B.id,B.date FROM B WHERE B.id=A.id order by B.Date desc ) t1 where rownum<=1 AND t1.Date between sysdate-100 and sysdate and t1.id=A.id) the sql likes this will be good. there are some errors in your sql.
1.it's no meaning of using "order by" in exists clause.
2.the sub sql must be a related query in exists clause,or the dadabase will return all results.
i think there exists better sql.
select A.*,row_number() over(order by B.Date desc) from A,B WHERE B.id=A.id rownum<=1 AND B.Date between sysdate-100 and sysdate)Maybe, you can try this, it depends on your specific data.
select A.*,row_number() over(order by B.Date desc) from A,B WHERE B.id=A.id rownum<=1 AND B.Date between sysdate-100 and sysdate)Maybe, you can try this, it depends on your specific data.
你那么用rownum没有用的。同一级语句里rownum在儿的order by 前面。是先去了前n行然后排序的。
其次我们可以换个思维方式。a和b关联后 b最后一条记录在100天内,和有一条记录在100天内其实等价的。所以根本就补不需要排序神马的。最后一条的目的其实是去除 selelct * from a 的重复列。看你的语句你想表达这样一个目的。从a里面检索这样的行,这一行的id在b的(最近一百天出现过的id中)。
so 按这个条件写就行了。 提出好的问题很重要。select * from a where a.id in (select distinct(id) from b where b.date between sysdate-100 and sysdate and b.id is not null)
不用担心not in 优化器会自动转化成嵌套查询或排序联合。
select * from a where a.id in
(
select id from b --gruop by 后就不用distinct了
where b.id is not null
gruop by id
having max(b.date) between sysdate-100 and sysdate
)
如果你不放心oracle 的优化器也可以吧子查询因子话自己做关联。
with c as
(
select id from b
where b.id is not null
gruop by id
having max(b.date) between sysdate-100 and sysdate
)
slelct a.*
from a ,c
where a.id=c.id
其实这个查询优化的关键就是用集合的思想解决问题。gruop by 要比(orader by +rownum)嵌套查询方便。
如果是desc的话oracle 会把null放在最前面。所以应该再加上 null last
select * from a不好意思我试试插入代码怎么用
select * from a