mysql sql语句 两张表查询(a最新记录时间 查询b最新值)

Question

[b][size=large]总体描述:根据交易记录表中的交易记录，得到汇率表中该交易记录时间后的第一条最新汇率[/size][/b]

交易记录表 (sale_log):
主键 币种 兑换金额 交易时间 交易日期
1 USD 100 10:08:23 2010-05-15
2 EUR 50 10:10:01 2010-05-15
3 USD 10 20:21:19 2010-05-15
4 USD 100 22:01:06 2010-05-15
5 USD 20 01:23:46 2010-05-16
6 EUR 10 12:07:06 2010-05-16

币种汇率表(rate):同一币种一天可能存在多个汇率,需要精确到秒

 币种           录入时间                             汇率值   
       USD     2010-05-15 08:00:05                 6.8315
      USD     2010-05-15 20:10:05                 6.8310
      EUR     2010-05-15 08:00:05                10.1261
      USD     2010-05-16 08:00:05                 6.8286
      EUR     2010-05-16 08:10:55                10.1207
      USD     2010-05-16 22:07:00                 6.7789
      EUR     2010-05-16 18:20:35                10.1211           

需要的结果是：以记录表为准，得到每条记录的汇率（得到 >= 每条记录产生的时间 的第一条汇率)

表展现为:
主键 币种 兑换金额 交易时间 交易日期 记录后最新汇率
1 USD 100 10:08:23 2010-05-15 6.8310
2 EUR 50 10:10:01 2010-05-15 10.1207
3 USD 10 20:21:19 2010-05-15 6.8286
4 USD 100 22:01:06 2010-05-15 6.8286
5 USD 20 01:23:46 2010-05-16 6.8286
6 EUR 10 12:07:06 2010-05-16 10.1211

Answer 1

不好意思，有点麻烦了，又嵌套一层行了
select x.id , x.bizhong, x.duihuan, x.shijian, x.riqi, x.huilv from(select a.*,b.id as id2,b.bizhong2,b.huilv,b.lurushijian ,(replace(replace(replace(b.lurushijian,'-',''),':',''),' ','')-replace(replace(concat(a.riqi,a.shijian),'-',''),':','')) as cha
FROM
a
inner join b ON a.bizhong=b.bizhong2
where (replace(replace(replace(b.lurushijian,'-',''),':',''),' ','')-replace(replace(concat(a.riqi,a.shijian),'-',''),':',''))>0
order by a.id) x inner join (select id as id3 ,min(cha) as cha3 from(select a.*,b.id as id2,b.bizhong2,b.huilv,b.lurushijian ,(replace(replace(replace(b.lurushijian,'-',''),':',''),' ','')-replace(replace(concat(a.riqi,a.shijian),'-',''),':','')) as cha
FROM
a
left join b ON a.bizhong=b.bizhong2
where (replace(replace(replace(b.lurushijian,'-',''),':',''),' ','')-replace(replace(concat(a.riqi,a.shijian),'-',''),':',''))>0
order by a.id) c group by id) y on x.id=y.id3 and x.cha=y.cha3