分组统计并排序的问题

[march22]

数据表的字段格式如下：

row dept total date
--------------------------------
1 a部门 20 2005-06-02
2 b部门 12 2005-06-03
3 c部门 40 2005-06-20
4 a部门 21 2005-07-03
5 a部门 25 2004-12-21
6 b部门 58 2004-03-15
7 c部门 12 2005-07-08
8 c部门 55 2005-12-27
9 c部门 20 2005-11-21
10 a部门 42 2005-12-27
---------------------------------

现在需要按部门分组，统计年度数据，并且按年度统计数据的大小排序。
请帮我写出Sql语句，谢谢。

[子陌红尘]

declare @t table(row int,dept varchar(10),total int,date datetime)

insert into @t select 1 ,'a部门',20,'2005-06-02'
insert into @t select 2 ,'b部门',12,'2005-06-03'
insert into @t select 3 ,'c部门',40,'2005-06-20'
insert into @t select 4 ,'a部门',21,'2005-07-03'
insert into @t select 5 ,'a部门',25,'2004-12-21'
insert into @t select 6 ,'b部门',58,'2004-03-15'
insert into @t select 7 ,'c部门',12,'2005-07-08'
insert into @t select 8 ,'c部门',55,'2005-12-27'
insert into @t select 9 ,'c部门',20,'2005-11-21'
insert into @t select 10,'a部门',42,'2005-12-27'


select
a.dept,a.[year],total=sum(a.total)
from
(select dept,total,[year]=year(date) from @t) a
group by
a.dept,a.[year]
order by
a.dept,total desc


/*
dept year total
---------- ----------- -----------
a部门 2005 83
a部门 2004 25
b部门 2004 58
b部门 2005 12
c部门 2005 127
*/

[zlp321002]

--测试环境
declare @t table(row int identity(1,1),dept varchar(10),total int,date datetime)
insert into @t select 'a部门',20,'2005-06-02'
union all select 'b部门',12,'2005-06-03'
union all select 'c部门',40,'2005-06-20'
union all select 'a部门',21,'2005-07-03'
union all select 'a部门',25,'2004-12-21'
union all select 'b部门',58,'2004-03-15'
union all select 'c部门',12,'2005-07-08'
union all select 'c部门',55,'2005-12-27'
union all select 'c部门',20,'2005-11-21'
union all select 'a部门',42,'2005-12-27'
--查询
select
dept=dept,
total=sum(total),
date=year(date)
from @t
group by dept,year(date)
order by dept asc,total desc
--结果
dept total date
---------- ----------- -----------
a部门 83 2005
a部门 25 2004
b部门 58 2004
b部门 12 2005
c部门 127 2005

（所影响的行数为 5 行）

[pclogic]

mark