create table test1
(
id number,
num number,
logdate date
);
insert into test1 values(1,10,to_date('2005-09-12','yyyy-mm-dd'));
insert into test1 values(2,20,to_date('2005-09-11','yyyy-mm-dd'));
insert into test1 values(3,30,to_date('2005-09-10','yyyy-mm-dd'));
commit;create table test2
(
id number,
total number,
logdate date
);
insert into test2 values(1,10,to_date('2005-09-12 1','yyyy-mm-dd hh24'));
insert into test2 values(2,20,to_date('2005-09-11 1','yyyy-mm-dd hh24'));
insert into test2 values(3,30,to_date('2005-09-10 1','yyyy-mm-dd hh24'));
insert into test2 values(2,40,to_date('2005-09-12 1','yyyy-mm-dd hh24'));
commit;-------------------------------
执行如下代码:
select a.id,sum(num),sum(total) from test1 a,test2 b
where a.id=b.id
and a.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and a.logdate<to_date('2005-9-13','yyyy-mm-dd')
and b.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and b.logdate<to_date('2005-9-13','yyyy-mm-dd')
group by a.id
得到的结果会出人意了。各位实验下,可考虑增加test1,test2中的数据,看能得出什么结论
(
id number,
num number,
logdate date
);
insert into test1 values(1,10,to_date('2005-09-12','yyyy-mm-dd'));
insert into test1 values(2,20,to_date('2005-09-11','yyyy-mm-dd'));
insert into test1 values(3,30,to_date('2005-09-10','yyyy-mm-dd'));
commit;create table test2
(
id number,
total number,
logdate date
);
insert into test2 values(1,10,to_date('2005-09-12 1','yyyy-mm-dd hh24'));
insert into test2 values(2,20,to_date('2005-09-11 1','yyyy-mm-dd hh24'));
insert into test2 values(3,30,to_date('2005-09-10 1','yyyy-mm-dd hh24'));
insert into test2 values(2,40,to_date('2005-09-12 1','yyyy-mm-dd hh24'));
commit;-------------------------------
执行如下代码:
select a.id,sum(num),sum(total) from test1 a,test2 b
where a.id=b.id
and a.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and a.logdate<to_date('2005-9-13','yyyy-mm-dd')
and b.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and b.logdate<to_date('2005-9-13','yyyy-mm-dd')
group by a.id
得到的结果会出人意了。各位实验下,可考虑增加test1,test2中的数据,看能得出什么结论
看这个就明白了
select a.id,num,total from testaa a,testbb b
where a.id=b.id
and a.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and a.logdate<to_date('2005-9-13','yyyy-mm-dd')
and b.logdate>=to_date('2005-9-11','yyyy-mm-dd')
and b.logdate<to_date('2005-9-13','yyyy-mm-dd')1 10 10
2 20 20
2 20 40
test1中的sum(num)就会是不关联TEST2,单独求值的N倍;对TEST2来说,反之亦然.
以前没注意,以为ORACLE能分步解析 :)