有两个表:(tep 和 tmp)
tep有字段: id name type time
1 test 设计 2009-6-16 15:42:27
2 test 其他 2009-6-17 18:42:27
tmp有字段: id name filename time
1 test B1 2009-6-16 15:42:27
2 test B2 2009-6-16 18:42:27
3 test B4 2009-6-16 18:42:27
要的查询结果:name filename type time
test B1 设计 2009-6-16 15:42:27
test B2 设计 2009-6-16 18:42:27
test B4 设计 2009-6-16 18:42:27
(两个表的id不相同,用name和time关联)
tep有字段: id name type time
1 test 设计 2009-6-16 15:42:27
2 test 其他 2009-6-17 18:42:27
tmp有字段: id name filename time
1 test B1 2009-6-16 15:42:27
2 test B2 2009-6-16 18:42:27
3 test B4 2009-6-16 18:42:27
要的查询结果:name filename type time
test B1 设计 2009-6-16 15:42:27
test B2 设计 2009-6-16 18:42:27
test B4 设计 2009-6-16 18:42:27
(两个表的id不相同,用name和time关联)
from tep a,tmp b
where a.name=b.name and a.time=b.time
日期型的话这样写
select a.name,b.filename,a.type,a.time
from tep a,tmp b
where a.name=b.name
and to_char(a.time,'yyyymmdd')=to_char(b.time,'yyyymmdd')
select a.name,b.filename,a.type,a.time
from tep a,tmp b
where a.name=b.name
and substr(a.time,instr(a.time,' '))=substr(b.time,instr(b.time,' '))
2 test 其他 2009-6-17 18:42:27 楼主应该没有弄错吧这样的话可能tep表类似日程表
select tmp.*,
(select type from tep a where (name,time)
in (select b.name,max(b.time) from tep b where b.name=tmp.name and b.time<=tmp.time)
)as type
from tmp