tb_A
Name1 Num1 Name2 Num2
AA 20 a1 50
BB 80 a2 70
AA 50 a3 80
CC 30 a1 50
BB 60 a1 80
AA 40 a3 20
CC 20 a2 30
CC 30 a4 30汇总的条件是:
Num1汇总时按Name1来分类
Num2汇总时按Name2来分类得到如下样式的表格:得到表 tb_B
Name1 Num1 Name2 Num2
AA 110 a1 180
BB 140 a2 100
CC 80 a3 100
a4 30
Name1 Num1 Name2 Num2
AA 20 a1 50
BB 80 a2 70
AA 50 a3 80
CC 30 a1 50
BB 60 a1 80
AA 40 a3 20
CC 20 a2 30
CC 30 a4 30汇总的条件是:
Num1汇总时按Name1来分类
Num2汇总时按Name2来分类得到如下样式的表格:得到表 tb_B
Name1 Num1 Name2 Num2
AA 110 a1 180
BB 140 a2 100
CC 80 a3 100
a4 30
select name2,sum(num2) num2 from tb_a group by name2
insert @tb_A select 'BB', 80 , 'a2' , 70
insert @tb_A select 'AA' , 50 , 'a3' , 80
insert @tb_A select 'CC' , 30 , 'a1' , 50
insert @tb_A select 'BB' , 60 , 'a1' , 80
insert @tb_A select 'AA' , 40 , 'a3' , 20
insert @tb_A select 'CC' , 20 , 'a2' , 30
insert @tb_A select 'CC' , 30 , 'a4' , 30 select px=identity(int,1,1), Name1,Num1=sum(Num1) into #1 from @tb_A group by name1 order by name1select px=identity(int,1,1), Name2,Num2=sum(Num2) into #2 from @tb_A group by name2 order by name2select name1,num1,name2,num2 from #1 a full join #2 b on a.px=b.px drop table #1,#2/*
name1 num1 name2 num2
----- ----------- ----- -----------
AA 110 a1 180
BB 140 a2 100
CC 80 a3 100
NULL NULL a4 30(所影响的行数为 4 行)
*/
if object_id('[tb_A]') is not null drop table [tb_A]
go
create table [tb_A]([Name1] varchar(2),[Num1] int,[Name2] varchar(2),[Num2] int)
insert [tb_A]
select 'AA',20,'a1',50 union all
select 'BB',80,'a2',70 union all
select 'AA',50,'a3',80 union all
select 'CC',30,'a1',50 union all
select 'BB',60,'a1',80 union all
select 'AA',40,'a3',20 union all
select 'CC',20,'a2',30 union all
select 'CC',30,'a4',30
---查询---
select id=identity(int,1,1),name1,sum(num1)as num1 into #1 from tb_a group by name1
select id=identity(int,1,1),name2,sum(num2)as num2 into #2 from tb_a group by name2select
isnull(a.name1,'') as name1,
isnull(ltrim(a.num1),'') as num1,
isnull(b.name2,'') as name2,
isnull(ltrim(b.num2),'') as num2
from
#1 a
full join
#2 b
on
a.id=b.id
---结果---/**
name1 num1 name2 num2
----- ------------ ----- ------------
AA 110 a1 180
BB 140 a2 100
CC 80 a3 100
a4 30(所影响的行数为 4 行)
**/
select id=identity(int,1,1), name2,sum(num2)as num2 into two from tab_a group by name2
select name1,num1,name2,num2 from two full join one on one.id=two.id
select name1,sum(num1) num1 from tb_a group by name1 union all
select name2,sum(num2) num2 from tb_a group by name2 后列转行: