例表1
A C
AA 1 3
CC 7 9
合计 8 12例表2
A B
AA 1 2
BB 4 5
合计 5 7希望同行同列的相加,其他的不变
A B C
AA 2 2 3
BB 4 5
CC 7 9
合计 13 7 12用的excel的sheet表
有办法做吗?谢谢
A C
AA 1 3
CC 7 9
合计 8 12例表2
A B
AA 1 2
BB 4 5
合计 5 7希望同行同列的相加,其他的不变
A B C
AA 2 2 3
BB 4 5
CC 7 9
合计 13 7 12用的excel的sheet表
有办法做吗?谢谢
union
select * from 表2 where a<>'合计'
union
select a='合计',b=sum(b),c=sum(c)
from (select * from 表1 where a='合计'
union
select * from 表2 where a='合计')z
-- Author :SQL77(只为思齐老)
-- Date :2010-01-30 11:44:40
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] varchar(2),[C] int,[C3] int)
insert #TB
select 'AA',1,3 union all
select 'CC',7,9
--> 测试数据:#TB1
if object_id('tempdb.dbo.#TB1') is not null drop table #TB1
go
create table #TB1([A] varchar(2),[B] int,[C3] int)
insert #TB1
select 'AA',1,2 union all
select 'BB',4,5
--------------开始查询--------------------------
SELECT A,SUM(C)C,SUM(C3)C3 FROM
(
select * from #TB
UNION ALL
select * from #TB1)AS T GROUP BY A
UNION ALLSELECT '合计',SUM(C),SUM(C3) FROM
(
select * from #TB
UNION ALL
select * from #TB1)AS T----------------结果----------------------------
/* (所影响的行数为 2 行)
(所影响的行数为 2 行)A C C3
---- ----------- -----------
AA 2 5
BB 4 5
CC 7 9
合计 13 19(所影响的行数为 4 行)
*/
from (select a,b=sum(b),c=sum(c)
from (select * from 表1 where a<>'合计'
union
select * from 表2 where a<>'合计')k
group by a ) p
union
select a='合计',b=sum(b),c=sum(c)
from (select * from 表1 where a='合计'
union
select * from 表2 where a='合计')z
http://school.itzcn.com/index.html#
上面讲解的比较详细,希望对楼主有所帮助。