一个简单的查询有关问题,顶者有分
一个简单的查询问题,顶者有分
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
怎么样出来,多谢指点啊。
顶折有分。
------解决方案--------------------
先顶
------解决方案--------------------
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) value from tb group by name with rollup
------解决方案--------------------
- SQL code
select name,sum(value) as values from tb group by name union all select '小计',sum(value) as values from tb
------解决方案--------------------
- SQL code
select isnull(Name,'小计') as Name,sum(value) as value from 表 group by Name with rollup
------解决方案--------------------
- SQL code
select name,sum(value) value from tb group by name union all select '小计' name,sum(value) value from 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 value from @t group by Name with rollup /* Name value ---------- ---------------------------------------- A 28.0 B 27.0 小计 55.0 */
------解决方案--------------------
- SQL code
create table tb(ID int, Name varchar(101),value numeric(10,1)) insert into tb select 1, 'A', 1.0 union all select 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.0 B 27.0 合计 55.0 */ drop table tb