ID Name value
1 A 1.0
2 A 2.0
3 B 15.0
4 B 12.0
5 A 25.0
用一个SQL语句，查出Name为A和B的Value的和，结果如下：
Name Value
A 28.0
B 27.0
小计 55.0
我知道可以用
select distinct name,sum(value) as value from a group by name
但是这样只能列出
Name Value
A 28.0
B 27.0
下面的
小计 55.0
怎么样出来，多谢指点啊。
顶折有分。
------解决方案--------------------
先顶
------解决方案--------------------
- SQL code
select isnull(name,'小计') name,sum(value) valuefrom tbgroup by namewith rollup
------解决方案--------------------
- SQL code
select name,sum(value) as valuesfrom tbgroup by nameunion allselect '小计',sum(value) as valuesfrom tb
------解决方案--------------------
- SQL code
select isnull(Name,'小计') as Name,sum(value) as valuefrom 表group by Name with rollup
------解决方案--------------------
- SQL code
select name,sum(value) valuefrom tbgroup by nameunion allselect '小计' name,sum(value) valuefrom tb
------解决方案--------------------
- SQL code
declare @t table(ID int,Name varchar(10),value numeric(4,1)) insert into @t values(1,'A',1.0 )insert into @t values(2,'A',2.0 )insert into @t values(3,'B',15.0) insert into @t values(4,'B',12.0) insert into @t values(5,'A',25.0) select isnull(Name,'小计') as Name,sum(value) as valuefrom @tgroup by Name with rollup/*Name value ---------- ---------------------------------------- A 28.0B 27.0小计 55.0*/
------解决方案--------------------
- SQL code
create table tb(ID int, Name varchar(101),value numeric(10,1))insert into tbselect 1, 'A', 1.0 union allselect 2, 'A', 2.0 union all select 3, 'B', 15.0 union all select 4, 'B', 12.0 union all select 5, 'A', 25.0 select case when (grouping(name)=1) then '合计' else isnull(name,'unknow') end as name,sum(value) as value from tb group by name with cube /*name value-----------------A 28.0B 27.0合计 55.0*/drop table tb