如何将B表中的某一数据统计到A表中? insert into A(id,bm,yf) select id,bm,'200412' from B where rq in('2004-12-01','2004-12-31') 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 insert into A(id,bm,yf) select id,bm,'200412' from B where convert(varchar(10),rq,120) between '2004-12-01' and '2004-12-31' 谢谢!不过有点不好意思,我还是没表达清楚: 我的意思是将B表中的满足本月条件的记录je项累加到A表中,如果A表中存在,直接修改A表中的byje项,如果A表中不存在,那么就新增一条记录! 我以前一直都是用程序处理的,就是从B表中一条一条的读取数据,然后判断A表中是否存在,结果每次运行的时间都是很长,我对SQL只是了解,不熟悉,特向各位大虾讨教! 全部测试数据如下:create table A( [ID] [int] IDENTITY (1, 1) NOT NULL , bm varchar(20), yf varchar(6), ycje money, byje money, ymje money)create table B( [ID] [int] IDENTITY (1, 1) NOT NULL , bm varchar(20), rq datetime, je money)delete from Adelete from Binsert into A(bm,yf,ycje,byje,ymje) values ('01','200411',10,10,20)insert into A(bm,yf,ycje,byje,ymje) values ('02','200411',0,-20,-20)insert into A(bm,yf,ycje,byje,ymje) values ('03','200411',0,30,30)insert into A(bm,yf,ycje,byje,ymje) values ('04','200411',0,-40,-40)Insert Into B(bm,rq,je) values('01','2004-12-1 12:20:20',100)Insert Into B(bm,rq,je) values('01','2004-12-31 12:20:20',100)Insert Into B(bm,rq,je) values('02','2004-12-1 12:20:20',100)Insert Into B(bm,rq,je) values('02','2004-12-2 12:20:20',100)Insert Into B(bm,rq,je) values('02','2004-12-3 12:20:20',100)Insert Into B(bm,rq,je) values('12','2004-12-1 12:20:20',-100)Insert Into B(bm,rq,je) values('12','2004-12-31 12:20:20',300)Insert Into B(bm,rq,je) values('13','2004-12-2 12:20:20',100)Insert Into B(bm,rq,je) values('13','2004-12-2 12:20:20',-100)select * from Aselect * from B--ID是自动编号的吧?--第一步:将上月发生,本月未发生的数据导入:Insert Into A(bm,yf,ycje,byje,ymje) select aa.b1,'200412',aa.j1,isnull(bb.byje,0),isnull(aa.j1,0)+isnull(bb.byje,0) from (select bm b1,ymje j1 from A where yf='200411') aa left outer join (select bm , sum(je) byje from B where (rq>='2004-12-1') and (rq<'2005-1-1') group by bm) bb on aa.b1=bb.bm--第二步:将上月未发生,本月发生的数据导入:Insert Into A(bm,yf,ycje,byje,ymje) select bb.bm,'200412',0,bb.byje,bb.byje from (select bm,sum(je) byje from B where (rq>='2004-12-1' and rq<'2005-1-1')and(bm not in (select bm from A where yf='200412')) group by bm) bb select * from A order by yf,bm测试结果如下:A,B表:ID bm yf ycje byje ymje ----------- -------------------- ------ --------------------- --------------------- --------------------- 56 01 200411 10.0000 10.0000 20.000057 02 200411 .0000 -20.0000 -20.000058 03 200411 .0000 30.0000 30.000059 04 200411 .0000 -40.0000 -40.0000(所影响的行数为 4 行)ID bm rq je ----------- -------------------- ------------------------------------------------------ --------------------- 39 01 2004-12-01 12:20:20.000 100.000040 01 2004-12-31 12:20:20.000 100.000041 02 2004-12-01 12:20:20.000 100.000042 02 2004-12-02 12:20:20.000 100.000043 02 2004-12-03 12:20:20.000 100.000044 12 2004-12-01 12:20:20.000 -100.000045 12 2004-12-31 12:20:20.000 300.000046 13 2004-12-02 12:20:20.000 100.000047 13 2004-12-02 12:20:20.000 -100.0000(所影响的行数为 9 行)最后结果:ID bm yf ycje byje ymje ----------- -------------------- ------ --------------------- --------------------- --------------------- 56 01 200411 10.0000 10.0000 20.000057 02 200411 .0000 -20.0000 -20.000058 03 200411 .0000 30.0000 30.000059 04 200411 .0000 -40.0000 -40.000060 01 200412 20.0000 200.0000 220.000061 02 200412 -20.0000 300.0000 280.000062 03 200412 30.0000 .0000 30.000063 04 200412 -40.0000 .0000 -40.000064 12 200412 .0000 200.0000 200.000065 13 200412 .0000 .0000 .0000 SQL日期问题 两个表的查询 execute sp_executesql 一次可以执行多句SQL语句吗 求需多个like的sql语句 请教一下!!! 如何使用PATINDEX函数!!!!!急急急急急!!!!!! 金额型数据如何方便地显示出来 *******高分求征(各位进来看看)******** 字段转换问题 help? 请高手帮忙看看连接sqlserver数据库问题 请推荐一些SQL语言的进阶参考资料
select id,bm,'200412' from B where convert(varchar(10),rq,120) between '2004-12-01' and '2004-12-31'
我的意思是将B表中的满足本月条件的记录je项累加到A表中,如果A表中存在,直接修改A表中的byje项,如果A表中不存在,那么就新增一条记录!
[ID] [int] IDENTITY (1, 1) NOT NULL ,
bm varchar(20),
yf varchar(6),
ycje money,
byje money,
ymje money)create table B(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
bm varchar(20),
rq datetime,
je money)delete from A
delete from Binsert into A(bm,yf,ycje,byje,ymje) values ('01','200411',10,10,20)
insert into A(bm,yf,ycje,byje,ymje) values ('02','200411',0,-20,-20)
insert into A(bm,yf,ycje,byje,ymje) values ('03','200411',0,30,30)
insert into A(bm,yf,ycje,byje,ymje) values ('04','200411',0,-40,-40)Insert Into B(bm,rq,je) values('01','2004-12-1 12:20:20',100)
Insert Into B(bm,rq,je) values('01','2004-12-31 12:20:20',100)
Insert Into B(bm,rq,je) values('02','2004-12-1 12:20:20',100)
Insert Into B(bm,rq,je) values('02','2004-12-2 12:20:20',100)
Insert Into B(bm,rq,je) values('02','2004-12-3 12:20:20',100)
Insert Into B(bm,rq,je) values('12','2004-12-1 12:20:20',-100)
Insert Into B(bm,rq,je) values('12','2004-12-31 12:20:20',300)
Insert Into B(bm,rq,je) values('13','2004-12-2 12:20:20',100)
Insert Into B(bm,rq,je) values('13','2004-12-2 12:20:20',-100)
select * from A
select * from B
--ID是自动编号的吧?
--第一步:将上月发生,本月未发生的数据导入:
Insert Into A(bm,yf,ycje,byje,ymje) select aa.b1,'200412',aa.j1,isnull(bb.byje,0),isnull(aa.j1,0)+isnull(bb.byje,0) from (select bm b1,ymje j1 from A where yf='200411') aa left outer join (select bm , sum(je) byje from B where (rq>='2004-12-1') and (rq<'2005-1-1') group by bm) bb on aa.b1=bb.bm
--第二步:将上月未发生,本月发生的数据导入:
Insert Into A(bm,yf,ycje,byje,ymje) select bb.bm,'200412',0,bb.byje,bb.byje from (select bm,sum(je) byje from B where (rq>='2004-12-1' and rq<'2005-1-1')and(bm not in (select bm from A where yf='200412')) group by bm) bb select * from A order by yf,bm测试结果如下:
A,B表:
ID bm yf ycje byje ymje
----------- -------------------- ------ --------------------- --------------------- ---------------------
56 01 200411 10.0000 10.0000 20.0000
57 02 200411 .0000 -20.0000 -20.0000
58 03 200411 .0000 30.0000 30.0000
59 04 200411 .0000 -40.0000 -40.0000(所影响的行数为 4 行)ID bm rq je
----------- -------------------- ------------------------------------------------------ ---------------------
39 01 2004-12-01 12:20:20.000 100.0000
40 01 2004-12-31 12:20:20.000 100.0000
41 02 2004-12-01 12:20:20.000 100.0000
42 02 2004-12-02 12:20:20.000 100.0000
43 02 2004-12-03 12:20:20.000 100.0000
44 12 2004-12-01 12:20:20.000 -100.0000
45 12 2004-12-31 12:20:20.000 300.0000
46 13 2004-12-02 12:20:20.000 100.0000
47 13 2004-12-02 12:20:20.000 -100.0000(所影响的行数为 9 行)最后结果:ID bm yf ycje byje ymje
----------- -------------------- ------ --------------------- --------------------- ---------------------
56 01 200411 10.0000 10.0000 20.0000
57 02 200411 .0000 -20.0000 -20.0000
58 03 200411 .0000 30.0000 30.0000
59 04 200411 .0000 -40.0000 -40.0000
60 01 200412 20.0000 200.0000 220.0000
61 02 200412 -20.0000 300.0000 280.0000
62 03 200412 30.0000 .0000 30.0000
63 04 200412 -40.0000 .0000 -40.0000
64 12 200412 .0000 200.0000 200.0000
65 13 200412 .0000 .0000 .0000