从表b里面有多条记录,里面有时间字段t1,和部门字段d1,和主表a用两个关键字段关联k1,k2,如何查出最近的部门,即时间最靠后的部门呢?
select a.k1,a.k2,b.d1 from a
left join b on a.k1=b.k1 and a.k2=b.k2
select a.k1,a.k2,b.d1 from a
left join b on a.k1=b.k1 and a.k2=b.k2
解决方案 »
- OracleDBConsole服务启动不了,又该怎么删除oracle中的EM 资料档案库?
- 请高手作答 **** 要具体实现 [谢谢]
- 大家帮我看看 oracle自定义函数代码哪儿写错了?
- Oracle的SQL语句里面有没有类似mysql里Auto_Increment的语句?
- 请问我想取出记录集的第30之60条记录,应该怎么写呢?我写成where rownum>30 and rownum<60为什么选不出来纪录呢?
- 这个SQL语句为什么这么慢
- 如何取多条的avg把为0的数据去掉
- 取指定数据行?
- 怎么设定行数?
- ORACLE8I DOS 中使用的命令有哪些?
- Oracle 的外键用法
- 求一条SQL语句
select k1,k2,d1 from(
select a.k1,a.k2,b.d1 from a
left join b on a.k1=b.k1 and a.k2=b.k2 order by b.t1 desc)
where rownum=1
left join b as x on a.k1=x.k1 and a.k2=x.k2
Where not exists(Select * from b Where d1=x.d1 and t1>x.t1)
Inner join b as x on a.k1=x.k1 and a.k2=x.k2
Where not exists(Select * from b Where d1=x.d1 and t1>x.t1)
要不
select a.k1,a.k2,b.d1 from a
left join b on a.k1=b.k1 and a.k2=b.k2
where b.t1=(select max(t1) from b)
where a.k1=b.k1(+) and a.k2=b.k2(+) and b.t1=(select max(t1) from b)b.t1要建索引
from a,b
where d1 =
( select max(d1)
from
(
select a.k1,a.k2,b.d1 from a
left join b on a.k1=b.k1 and a.k2=b.k2
)
)
and a,k1 = b.k1 and a.k2 = b.k2;
left join b on a.k1=b.k1 and a.k2=b.k2 order by b.t1 desc) where rownum=1;
2、如果这个表是自己设计的,可以把该字段设置为浮点,然后再建立索引,这样所建立的索引是惯用,可以使用排序的方法。
3、既然:表b里面有多条记录,里面有时间字段t1,和部门字段d1,那么为什么还需要主表a呢,我不是很明白。
"b.t1建立索引不管用,因为是时间字段。"
建议你再去学学数据库的基础
select Select a.k1,a.k2,x.d1 from a
left join b as x on a.k1=x.k1 and a.k2=x.k2
where b.t1 = (select max(t1) from b)
这样就行了。
select Select a.k1,a.k2,x.d1 from a
left join b as x on a.k1=x.k1 and a.k2=x.k2
where x.t1 = (select max(t1) from b)