求一个汇总查询问题 在线等....
a 表
id name type my1 my2
-------------------------------
1 aa taa 20 30
2 bb tbb 20 30
3 cc taa 30 40
4 dd tbb 40 50
5 ee taa 30 40
6 ff taa 50 60
...... ......
t 表
tn name
-----------------------
taa A型
tbb B型
...... ......
想要结果:
type my1 my2
-----------------------
A型 130 170
B型 60 80
...... ......
------解决方案--------------------create table a(id int, name varchar(10), type varchar(10), my1 int, my2 int)
insert a select 1, 'aa ', 'taa ', 20, 30
union all select 2, 'bb ', 'tbb ', 20, 30
union all select 3, 'cc ', 'taa ', 30, 40
union all select 4, 'dd ', 'tbb ', 40, 50
union all select 5, 'ee ', 'taa ', 30, 40
union all select 6, 'ff ', 'taa ', 50, 60
create table t(tn varchar(10), name varchar(10))
insert t select 'taa ', 'A型 '
union all select 'tbb ', 'B型 '
select name, my1=isnull(my1, 0), my2=isnull(my2, 0)
from t
left join
(
select type, my1=sum(my1), my2=sum(my2)
from a
group by type
)a on t.tn=a.type
union all
select '合计 ', sum(my1), sum(my2) from a
--result
name my1 my2
---------- ----------- -----------
A型 130 170
B型 60 80
合计 190 250
(3 row(s) affected)