请问一条sql语句的写法

[ahking]

表1结构:

rq bm a1 b1
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-1 3 200 100
2009-6-2 1 20 60

表2结构:

rq bm a2 b2
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-2 1 20 60

想实现这样的结果，按日期将bm相同的a1和a2形成新字段a=a1+a2，b1和b2形成新字段b=b1+b2，
同时添加一条记录，把每天不同bm的a和b加起来，把其bm设为0，这样的sql语句怎样写呢？

[william3033]

一、
select t1.rq,t1.bm,a1+a2 as a,b1+b2 as b from t1,t2 where t1.rq=t2.rq and t1.bm=t2.bm

[hjianke]

insert into table 
select a+b,'0' from (select (A.a1+B.a2) as a,(A.b1+B.b2) as b
                     from 表1 A,表2 B
                     where A.rq=B.rq and A.bm=B.bm);

这样试试

[hjianke]

少了个日期字段

insert into table 
select a+b,'0',rq from (select (A.a1+B.a2) as a,(A.b1+B.b2) as b,rq
                     from 表1 A,表2 B
                     where A.rq=B.rq and A.bm=B.bm);

[william3033]

二、
select c.rq,'0',c.sa+d.sa,c.sb+d.sb from
select rq,sum(a1) as sa,sum(b1) as sb from t1 a where not exists (select 1 from (select t1.rq,t1.bm from t1,t2 where t1.rq=t2.rq and t1.bm=t2.bm) b where a.rq=b.rq and a.bm=b.bm group by rq ) c,

select rq,sum(a2) as sa,sum(b2) as sb from t2 a where not exists (select 1 from (select t1.rq,t1.bm from t1,t2 where t1.rq=t2.rq and t1.bm=t2.bm) b where a.rq=b.rq and a.bm=b.bm group by rq) d where c.rq=d.rq

[felicity_h]

select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm;

select a.rq,'0' bm,a+b c
from (select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm) c

[ahking]

这些功能要求一条sql语句实现，返回需要的结果

[bw555]

楼主把想要生成的结果，写出来吧，实在猜不出想要什么结果
囧

[ahking]

表1结构:

rq bm a1 b1
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-1 3 200 100
2009-6-2 1 20 60

表2结构:

rq bm a2 b2
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-2 1 20 60


查询结果:
rq bm a b
2009-6-1 0 520 240
2009-6-1 1 200 100
2009-6-1 2 120 40
2009-6-1 3 200 100
2009-6-2 0 40 120
2009-6-2 1 40 120

[welyngj]

select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm
union all
select a.rq,0,sum(a) a,sum(b) b
from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm)t group by a.rq,0;

[welyngj]

select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm
union all
select a.rq,0,sum(a) a,sum(b) b
from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm)t  group by a.rq;

[bw555]

up
[Quote=引用 10 楼 welyngj 的回复:]
SQL codeselect a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm
union all
select a.rq,0,sum(a) a,sum(b) b
from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm)t group by a.rq;
[/Quote]

[ahking]

奇怪,提示错误,a.rq invalid identifier

[ahking]

是group by 后面的a.rq报错

[welyngj]

select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm
union all
select a.rq,0,sum(a) a,sum(b) b
from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm)t group by t.rq;

[ahking]

还有union all select t.rq......
对吧

[ahking]

还有个问题，如果我要拼接sql语句，在最后面加条件，比如说时间条件
用and 和where 都不对，是否只能在每个select的where中加条件啊

[welyngj]

select * from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm
union all
select a.rq,0,sum(a) a,sum(b) b
from
(select a.rq,a.bm,a.a1+b.a1 a,a.b1+b.b1 b
from 表1 a,表2 b
where a.rq=b.rq and a.bm=b.bm)t group by t.rq) t2 where t.rq='xxxxxx'

[ahking]

突然发现一个问题,如果某天表2没有表1中某个bm的数据,那么该bm求和的数据就会没有啊，是不是该用连接操作啊
表1结构:

rq bm a1 b1
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-1 3 200 100
2009-6-2 1 20 60

表2结构:

rq bm a2 b2
2009-6-1 1 100 50
2009-6-1 2 60 20
2009-6-2 1 20 60


查询结果会不会就成了这样:
rq bm a b
2009-6-1 0 520 240
2009-6-1 1 200 100
2009-6-1 2 120 40
2009-6-2 0 40 120
2009-6-2 1 40 120

正确的应该是这样啊

查询结果:
rq bm a b
2009-6-1 0 520 240
2009-6-1 1 200 100
2009-6-1 2 120 40
2009-6-1 3 200 100
2009-6-2 0 40 120
2009-6-2 1 40 120