[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
不好意思，有点麻烦了，又嵌套一层行了
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
交易时间,交易日期 ,录入时间 字段类型是什么？什么数据库？oracle？
看看下面这个，找个你的表的字段改改
select id ,bizhong,duihuan,shijian,riqi,huilv,min(cha) 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
sale_log a
left join rate 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
我自己建的表，测试完全正确
CREATE TABLE a (id int(11) NOT NULL,bizhong varchar(255) default NULL,duihuan double default NULL,shijian varchar(255) NOT NULL,riqi date NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
相当于你的交易记录表
CREATE TABLE b (id int(11) NOT NULL auto_increment,bizhong2 varchar(30) default NULL,lurushijian datetime NOT NULL,huilv double NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;
相当于你的币种汇率表
数据是你的数据，执行的是下面的语句
select id ,bizhong,duihuan,shijian,riqi,huilv,min(cha) 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 a
left join b 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
没问题记得给分，有问题再说
[quote]INSERT INTO b VALUES (2,'2','2010-05-14 10:49:33',11.56); [/quote][quote]INSERT INTO b VALUES (8,'2','2010-05-14 20:00:07',11.44); [/quote][quote]INSERT INTO a VALUES (2,'2',50,'11:55:19','2010-05-14'); [/quote]
我运行取得时11.44这条数据，不是15号的，另外交易后的数据，明显不能是11.56这条数据，这条数据在交易前插入的，难道我发错了语句？
select id ,bizhong,duihuan,shijian,riqi,huilv,min(cha) 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
select *, max(h.dt) from (select id,rate.Currency,exchange, date,time, value,datetime as dt from rate right join sale_log on rate.currency=sale_log.currency and concat(date,' ',time) <rate.DateTime) as h group by id
我这个可以，至少我能得到你需要的结果，不过选出来的列较多，你需要自己剔出一下。
[quote]11.56插入的时间是在交易后，而且是交易后的时间第一个，应该取这个数据，而不应该取
INSERT INTO b VALUES (8,'2','2010-05-14 20:00:07',11.44);
这两个数据都是交易后的汇率，而且11.56比这条早 [/quote]
我靠，你币种为2的这条数据交易时间是11:55:19，如下：
[quote]INSERT INTO a VALUES (2,'2',50,'11:55:19','2010-05-14');[/quote]
你交易时间是10:49:33，也就是说你插入在未交易前就插入了11.56这条数据
[quote]你交易时间是10:49:33，也就是说你插入在未交易前就插入了11.56这条数据[/quote]说错了，你插入b的时间是10:49:33
CREATE TABLE rate (bz varchar(20) NOT NULL,lrsj datetime NOT NULL,hlz float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE sale_log (id int(5) NOT NULL auto_increment,jysj time default NULL,bz varchar(20) default NULL,je float default NULL,jyrq date default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
你试一下：
SELECT s.bz,s.jysj,s.jyrq,s.je,r.hlz FROM rate r,(SELECT bz,jysj,jyrq,CONCAT(jyrq,' ',jysj) s_jytime,je FROM sale_log) s
WHERE s.s_jytime>(SELECT MAX(r.lrsj) lrsj FROM rate r,(SELECT bz,jysj,jyrq,CONCAT(jyrq,' ',jysj) s_jytime,je FROM sale_log) s
WHERE s.s_jytime>r.lrsj) AND r.bz=s.bz;
用我的数据自己测试一下，我只测试了一个
[quote]天哪，我已经简化数据库表了，刚才的交易表已经是虚拟出来的中间表了（涉及3张表的查询），
如果拼SQL语句的话，再加上这。。。，真的没有其他办法了吗？
我已经被这问题困扰三天了，再整不出我估计要疯了 [/quote]
要么写存数过程，要么程序里控制，一般不会直接这样处理的
干嘛不给三张表，给两张表不一定就方便，老自己查自己多麻烦
不是只有两张表得吗，怎么又多出一张
select * ,(select 汇率值 from rate r where r.币种=s.币种 order by 汇率值 desc limit(0, 1)) from sale_log s;