日期:2014-05-18  浏览次数:20528 次

求助一个多表汇总的问题
问题如下:
有A B C D 4个表 结构相同

字段如下 字段全部是文本型

如表A

货号 规格1的数量 规格2的数量 规格3的数量 数量合计 

Q111 3 2 3 8
Q222 3 1 5 9
Q333 1 0 1 2


要求将 A B C 的汇总结果存到表D 中

即 表ABC的规格1的合计存入表D规格1中


------解决方案--------------------
四个表名依次为atb1,atb2,atb3,atb4
SQL code

SELECT dbo.atb1.A, CAST(dbo.atb1.B AS int) + CAST(dbo.atb2.B AS int) AS B, 
      CAST(dbo.atb1.C AS int) + CAST(dbo.atb2.C AS int) AS C, CAST(dbo.atb1.D AS int) 
      + CAST(dbo.atb2.D AS int) AS D INTO TMPTAB
FROM dbo.atb1 INNER JOIN
      dbo.atb2 ON dbo.atb1.A = dbo.atb2.A
SELECT dbo.atb3.A, CAST(dbo.atb3.B AS int) + CAST(dbo.tmptab.B AS int) AS B, 
      CAST(dbo.atb3.C AS int) + CAST(dbo.tmptab.C AS int) AS C, CAST(dbo.atb3.D AS int) 
      + CAST(dbo.tmptab.D AS int) AS D INTO atb4
FROM dbo.atb3 INNER JOIN
      dbo.tmptab ON dbo.atb3.A = dbo.tmptab.A
drop table tmptab

------解决方案--------------------
SQL code
insert into D(货号,规格1的数量,规格2的数量,规格3的数量,数量合计)
select 
  货号,
  ltrim(sum(cast(规格1的数量 as int))),
  ltrim(sum(cast(规格2的数量 as int))),
  ltrim(sum(cast(规格3的数量 as int))), 
  ltrim(sum(cast(数量合计 as int)))
from
(select * from A
 union all
 select * from B
 union all
 select * from C
) t
group by 货号