对表先排序 然后汇总某列

[uiwq]

表 A

tem_name m_shu c_ti

name1 2 au
name2 12 ac
name3 20 as
name2 10 as
name1 25 av
name4 65 ak
name1 85 47





查询得到

tem_name m_shu c_ti

name1 2 au
name1 25 av
name1 85 47

name2 12 ac
name2 10 as
name3 20 as
name4 65 ak --排序没问题的


name1 112 --怎么得到 tem_name 中所有值为；name1对应 的m_shu列的和
name2 22 --同上 name2 的
name3 20
name4 65

//不知道有name1 name2 ……的存在，消除重复行后得到的就是所有要汇总的

大家帮忙，谢谢！

[sqlfan]

mark

[uiwq]

本意是想两个结果集合，能不能合在一起:）

[九斤半]

什么意思？

排序后的结果+汇总后的结果？？？

[allright_flash]

select tem_name,sum(m_shu) from 表A
group by tem_name order by tem_name

[allright_flash]

select tem_name,m_shu from 表A
order by tem_name


select tem_name,sum(m_shu) from 表A
group by tem_name order by tem_name

[hellowork]

----创建测试数据
declare @t table(tem_name varchar(10), m_shu int, c_ti varchar(10))
insert @t
select 'name1', 2, 'au' union all
select 'name2', 12, 'ac' union all
select 'name3', 20, 'as' union all
select 'name2', 10, 'as' union all
select 'name1', 25, 'av' union all
select 'name4', 65, 'ak' union all
select 'name1', 85, '47'

----查询
select top 100 percent tem_name,m_shu,c_ti
from
(
select *,0 as sortid from @t
union all
select tem_name,sum(m_shu),'',1 from @t group by tem_name
) as t order by sortid,tem_name

/*结果
tem_name m_shu c_ti
---------------------------------------------
name1 2 au
name1 25 av
name1 85 47
name2 12 ac
name2 10 as
name3 20 as
name4 65 ak
name1 112
name2 22
name3 20
name4 65
*/

[hellowork]

top 100 percent 可以省略

[allright_flash]

(select tem_name,m_shu from 表A
order by tem_name)
union all
(select tem_name,sum(m_shu) as m_shu from 表A
group by tem_name order by tem_name)

[新鲜鱼排]

希望这个有用
http://community.csdn.net/Expert/topic/5073/5073353.xml?temp=.159466