合并两个表的数据？(50分)

C

czlucky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-09

• #1

有两个互相独立，但数据结构相同，现需要将两个表的数据合并（插入/添加）到
一个新的表（也是相同结构）中，比如第一个表有100条记录，第二个有150条，
新表就有250条记录。另外新的表可以是只读的，不要用insert into,
但好像有view？

 

D

dlnew

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-09

• #2

create view tmp as
select fielda1 as f1, fielda2 as f2,... from a
union
select fieldb1 as f1, fieldb2 as f2,... from b

 

C

chenliang_fly

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-10

• #3

同意，用union作合并即可，好好参考数据库中关于union的用法，看看如何处理重复纪录

 

A

ATY

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-10

• #4

同意楼上！如果还有问题，不妨再提出来！

 

C

czlucky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-11

• #5

只能用local sql，表是paradox的，现用
select wareno,sum(number),sum(price) from stock group by wareno
union all
select wareno,sum(number),sum(price) from sell group by wareno
union all
....
order by wareno
获得合并了的数据，但多个表就有多条合并的记录，能不能再将合并后的数据集合在汇总，
即：将上面的数据集合中wareno相同的记录再进行合并，使最终的数据集合中wareno相同
的记录只有全部合并的一条记录？
用英文表达就是：SUM(Sum(stock.number)、sum(sell.number)、...)
由于上面的限制“local sql，paradox表”，所以不能用视图(view)！

 

A

antic_ant

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-11

• #6

table1.batchmoveBatchMove(table2, batAppend);

 

D

dlnew

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-12

• #7

select sum(field1) as f1, sum(field2) as f2 ...
from (select a1 as field1, a2 as field2, ... from tablea
unoin
select b1 as field1, b2 as field2, ... from tableb)

 

C

czlucky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2001-11-13

• #8

出错：
合并已成功，就是合并后的再SUM不能成功，
好象是不支持“嵌套查询”；
提示：
Invalid use of keyword
Token : SELECT
Line Number: 2.
SELECT aa.WareNo, SUM(aa.Number), SUM(aa.Price)
FROM
( SELECT WareNo as WareNo, SUM(Number) as Number, SUM(Number * Price) as Price FROM StockWare GROUP BY WareNo
UNION ALL
SELECT WareNo as WareNo, 0-SUM(Number) as Number, 0-SUM(Number * Price) as Price FROM SellWare GROUP BY WareNo
) as aa
Group by aa.wareno
ORDER BY aa.WareNo

请注意：“local sql；paradox表”