sql 查询

[u010703853]

请看下图：

可以不用 union 的方法吗？？？？

[Tiger_Zhao]

前台公式自己合计啊。
非得用SQL的话 UNION ALL 已经是最简单的了。

[时间都去哪了啊]

等待高手...

[hleb231]

select *,
      aaa+bbb as ccc 
from 
    (select isnull(place,'合计') as 'Place'   
           ,sum(aaa) as 'aaa' 
           ,sum(bba) as 'bbb'
      from table  
      group by place
    with rollup )a

[hleb231]

select *,
      aaa+bbb+ccc as　合计
from 
    (select isnull(place,'合计') as 'Place'   
           ,sum(aaa) as 'aaa' 
           ,sum(bbb) as 'bbb'
　　　      ,sum(ccc) as 'ccc'
      from table  
      group by place
    with rollup )a

[中国风]

;WITH Ta(Place,AAA,BBB,CCC)
AS
(
SELECT N'上海',111,222,333
UNION ALL
SELECT N'北京',100,100,100
UNION ALL
SELECT N'广州',89,78,67
)
SELECT ISNULL(Place,N'合计') AS Place,SUM(AAA) AS AAA,SUM(BBB) AS BBB,SUM(CCC) AS CCC FROM Ta GROUP BY Place WITH rollup 
/*
Place	AAA	BBB	CCC
北京	100	100	100
广州	89	78	67
上海	111	222	333
合计	300	400	500
*/