表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语句怎样写呢?
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语句怎样写呢?
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
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);这样试试
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);
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
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
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
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;
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;
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;
对吧
用and 和where 都不对,是否只能在每个select的where中加条件啊
(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'
表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