如题:create table test1 (
id int,
date_time date,
socum decimal(8,2)
)
insert into test1 values(1,date'2009-09-10',10);
insert into test1 values(2,date'2008-07-18',20);
insert into test1 values(3,date'2007-06-01',30);
select * from test1create table test2 (
hid int,
gdate_time date,
jsocum decimal(8,2)
)
insert into test2 values(1,date'2007-06-30',50);
insert into test2 values(2,date'2007-03-30',60);
insert into test2 values(3,date'2007-09-30',70);
insert into test2 values(4,date'2007-12-30',80);
insert into test2 values(1,date'2008-06-26',100);
求结果是要比test1中的date_time小且最近的日期在test2中
结果为: ID DATE_TIME SOCUM MAX_GDATE_TIME JSOCUM
--------------------------------------- ----------- ---------- -------------- ----------
1 2009-9-10 10.00 2008-6-26 100.00
2 2008-7-18 20.00 2008-6-26 100.00
3 2007-6-1 30.00 2007-3-30 60.00
解决方法:
--1.左连接
select x.id, x.date_time, x.socum, x.gdate_time, c.jsocum
from (
select a.id,
a.date_time,
a.socum,
max(b.gdate_time) gdate_time
from test1 a, test2 b
where a.date_time > b.gdate_time
group by a.id, a.date_time, a.socum
)x left join test2 c on x.gdate_time = c.gdate_time
order by x.id
高手们帮忙看下,还有其他的解决方案吗?
id int,
date_time date,
socum decimal(8,2)
)
insert into test1 values(1,date'2009-09-10',10);
insert into test1 values(2,date'2008-07-18',20);
insert into test1 values(3,date'2007-06-01',30);
select * from test1create table test2 (
hid int,
gdate_time date,
jsocum decimal(8,2)
)
insert into test2 values(1,date'2007-06-30',50);
insert into test2 values(2,date'2007-03-30',60);
insert into test2 values(3,date'2007-09-30',70);
insert into test2 values(4,date'2007-12-30',80);
insert into test2 values(1,date'2008-06-26',100);
求结果是要比test1中的date_time小且最近的日期在test2中
结果为: ID DATE_TIME SOCUM MAX_GDATE_TIME JSOCUM
--------------------------------------- ----------- ---------- -------------- ----------
1 2009-9-10 10.00 2008-6-26 100.00
2 2008-7-18 20.00 2008-6-26 100.00
3 2007-6-1 30.00 2007-3-30 60.00
解决方法:
--1.左连接
select x.id, x.date_time, x.socum, x.gdate_time, c.jsocum
from (
select a.id,
a.date_time,
a.socum,
max(b.gdate_time) gdate_time
from test1 a, test2 b
where a.date_time > b.gdate_time
group by a.id, a.date_time, a.socum
)x left join test2 c on x.gdate_time = c.gdate_time
order by x.id
高手们帮忙看下,还有其他的解决方案吗?
select a.*,(select min(jsocum) from test2 b where b.gdate_time<=a.date_time) from test1 a
不知道效率和min比较怎么样
--2.替换成表连接
select x.id, x.date_time, x.socum, x.gdate_time, c.jsocum
from (
select a.id,
a.date_time,
a.socum,
max(b.gdate_time) gdate_time
from test1 a, test2 b
where a.date_time > b.gdate_time
group by a.id, a.date_time, a.socum
)x , test2 c
where x.gdate_time = c.gdate_time
order by x.id;