如果现在有两个表分别是a和b,字段为a(a_id,a_no,a_date)、b(b_no,b_date,b_内容),其中a,b这两个表只有a_no和b_no是相互关联,如:
a(a_id,a_no,a_date) b(b_no,b_date,b_内容)
1 , 3311 , 2009-03-21 00:06:41 3311 , 2009-03-21 01:06:41 , m
2 , 3311 , 2009-03-21 11:06:41 3311 , 2009-03-21 11:36:41 , j
3 , 3312 , 2009-03-22 18:06:41 3312 , 2009-03-22 18:16:41 , u
4 , 3322 , 2009-03-23 21:06:41 3322 , 2009-03-23 21:10:41 , m
5 , 3322 , 2009-03-23 22:46:41 3312 , 2009-03-22 18:16:41 , m如上数据,a表中同一个a_no在同一天有多条记录,b表中同一个b_no在同一天也有多条记录,那么在PL/SQL中进行数据查询时怎么实现从时间上就近进行匹配。请高手指点迷津!!!
a(a_id,a_no,a_date) b(b_no,b_date,b_内容)
1 , 3311 , 2009-03-21 00:06:41 3311 , 2009-03-21 01:06:41 , m
2 , 3311 , 2009-03-21 11:06:41 3311 , 2009-03-21 11:36:41 , j
3 , 3312 , 2009-03-22 18:06:41 3312 , 2009-03-22 18:16:41 , u
4 , 3322 , 2009-03-23 21:06:41 3322 , 2009-03-23 21:10:41 , m
5 , 3322 , 2009-03-23 22:46:41 3312 , 2009-03-22 18:16:41 , m如上数据,a表中同一个a_no在同一天有多条记录,b表中同一个b_no在同一天也有多条记录,那么在PL/SQL中进行数据查询时怎么实现从时间上就近进行匹配。请高手指点迷津!!!
a(a_id,a_no,a_date) b(b_no,b_date,b_内容) 1 , 3311 , 2009-03-21 00:06:41 3311 , 2009-03-21 01:06:41 , m 2 , 3311 , 2009-03-21 11:06:41 3311 , 2009-03-21 11:36:41 , j 3 , 3312 , 2009-03-22 18:06:41 , 3312 2009-03-22 18:16:41 , u
4 , 3322 , 2009-03-23 21:06:41 3322 , 2009-03-23 21:10:41 , m
5 , 3322 , 2009-03-23 22:46:41 3312 , 2009-03-22 21:16:41 , m
select a.*,(select top 1 b_date from b
where a.b_no=b.b_no and a.a_date<=b.b_date order by b.b_date asc) as d,e.b_内容
from a left join b e on d.b_no=e.b_no and d.b_date=e.b_date
from (
select a.a_id,a.a_no,a.a_date,min(abs(a.a_date-b.b_date)) as mdv
from a inner join b on a.a_no=b.b_no
group by a.a_id,a.a_no,a.a_date) a2 inner join b b2 on a2.a_no=b2.b_no and abs(a2.a_date-b2.b_date)=a2.mdv
select a.*,(select top 1 b_date from b
where a.b_no=b.b_no and a.a_date <=b.b_date order by b.b_date asc) as d,e.b_内容
from a inner join b e on d.b_no=e.b_no and d.b_date=e.b_date
select a1.*,c.b_内容 from (
SELECT a_id,a_no,a_date,min(b.b_date) as mi,min(b.id) as mid from ba a left join bb1 b on a.a_no=b.b_no and a.a_date<=b.b_date
where b.b_no is not null
group by a_id,a_no,a_date ) a1
inner join bb1 c on c.b_no=a1.a_no and c.b_date=a1.mi and c.id=a1.mid
3312 , 2009-03-22 18:16:41 , m
为什么取U?
3312 , 2009-03-22 18:16:41 , m
这是写错的,应该是
3312 , 2009-03-22 18:16:41 , u
3312 , 2009-03-22 21:16:41 , m
SELECT a1.*, c.b_内容
FROM (SELECT a_id,a_no,a_date,min(b.b_date) as mi from ba a left join bb1 b on a.a_no=b.b_no and a.a_date<=b.b_date
where b.b_no is not null
group by a_id,a_no,a_date) AS a1 INNER JOIN bb1 AS c ON (c.b_date=a1.mi) AND (c.b_no=a1.a_no);上述代码假设日期、时间没有重复的