我发现 insert into aa(name,count) (SELECT [name], [count] FROM [C:\My Documents\db1.mdb].aa GROUP BY [name],[count] union SELECT [name], [count] FROM [C:\My Documents\db2.mdb].aa GROUP BY [name],[count]) 在access下调试通不过,但是 insert into aa(name,count) SELECT [name], [count] FROM [C:\My Documents\db1.mdb].aa GROUP BY [name],[count] 就可以,不过name相同的数据没有合并
hi xc(xc) , 根據你的提示,寫了下面這個語句,試試看。:)SELECT a.名稱, Sum(NZ(a.數量)+NZ(b.數量)) AS Expr1 FROM [C:\db1.mdb].ta AS a LEFT JOIN [C:\db2.mdb].ta AS b ON a.名稱 = b.名稱 GROUP BY a.名稱;
呃~!想想還是有錯誤。 Sorry,分兩步來做。1)、建一個查詢,并命名為tmpSELECT tmp.名稱, Sum(NZ(數量)) AS b數量 FROM [SELECT a.名稱,數量 FROM [C:\db1.mdb].ta AS a union all SELECT b.名稱,數量 FROM [C:\db2.mdb].ta AS b ]. AS tmp GROUP BY tmp.名稱;2)、新增資料:INSERT INTO ta(名稱, 數量) SELECT 名稱,b數量 FROM ab;
N_chow能否把上面两个帖子再贴一遍,怎么少了很多字符
1)、建一個查詢,并命名為tmpSELECT tmp.名稱, Sum(NZ(數量)) AS b數量 FROM [SELECT a.名稱,數量 FROM [C:\db1.mdb].ta AS a union all SELECT b.名稱,數量 FROM [C:\db2.mdb].ta AS b ]. AS tmp GROUP BY tmp.名稱;2)、新增資料:INSERT INTO ta(名稱, 數量) SELECT 名稱,b數量 FROM ab;
我举个例子吧 现在是要把数据合并 例如 f1.mdb name count a 10 b 20 f2.mdb name count a 20 c 15 我希望f3.mdb结果 name count a 30 b 20 c 15
N_chow,我怎么总是不能全部显示你的贴子?少了很多字。你是用一个SQL解决的吗?
我知道答案了! insert into aa(name,count) select name,sum(count) from( SELECT name, count FROM [C:\My Documents\db1.mdb].aa UNION ALL SELECT [name], [count] FROM [C:\My Documents\db2.mdb].aa) group by name
不要用union all就行了
以Delphi5为例,你可以用BatchMove控件,实现你所说的功能很简单,首先以覆盖形势将表1倒入表3,然后以更新方式将表2倒入表3,OK.
如果程序要经常执行本操作,当然每次都是更新表3,那么你可以每个表都以更新的方式倒入表3即可。
insert into aa(name,count)
(SELECT [name], [count]
FROM [C:\My Documents\db1.mdb].aa
GROUP BY [name],[count]
union
SELECT [name], [count]
FROM [C:\My Documents\db2.mdb].aa
GROUP BY [name],[count])
在access下调试通不过,但是
insert into aa(name,count)
SELECT [name], [count]
FROM [C:\My Documents\db1.mdb].aa
GROUP BY [name],[count]
就可以,不过name相同的数据没有合并
根據你的提示,寫了下面這個語句,試試看。:)SELECT a.名稱, Sum(NZ(a.數量)+NZ(b.數量)) AS Expr1
FROM [C:\db1.mdb].ta AS a LEFT JOIN [C:\db2.mdb].ta AS b ON a.名稱 = b.名稱
GROUP BY a.名稱;
Sorry,分兩步來做。1)、建一個查詢,并命名為tmpSELECT tmp.名稱, Sum(NZ(數量)) AS b數量
FROM [SELECT a.名稱,數量
FROM [C:\db1.mdb].ta AS a
union all
SELECT b.名稱,數量
FROM [C:\db2.mdb].ta AS b
]. AS tmp
GROUP BY tmp.名稱;2)、新增資料:INSERT INTO ta(名稱, 數量)
SELECT 名稱,b數量
FROM ab;
FROM [SELECT a.名稱,數量
FROM [C:\db1.mdb].ta AS a
union all
SELECT b.名稱,數量
FROM [C:\db2.mdb].ta AS b
]. AS tmp
GROUP BY tmp.名稱;2)、新增資料:INSERT INTO ta(名稱, 數量)
SELECT 名稱,b數量
FROM ab;
现在是要把数据合并
例如
f1.mdb
name count
a 10
b 20
f2.mdb
name count
a 20
c 15
我希望f3.mdb结果
name count
a 30
b 20
c 15
insert into aa(name,count)
select name,sum(count)
from(
SELECT name, count
FROM [C:\My Documents\db1.mdb].aa
UNION ALL SELECT [name], [count]
FROM [C:\My Documents\db2.mdb].aa)
group by name