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
----创建测试数据 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 */
top 100 percent 可以省略
(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)
order by tem_name
select tem_name,sum(m_shu) from 表A
group by tem_name order by tem_name
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
*/
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