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

[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

16个回答

不好意思,有点麻烦了,又嵌套一层行了
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

共16条数据 1 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!