sql中sum的烦恼

--> 数据库版本:

--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22

--> 测试数据：student

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'student') 

AND type in (N'U')) 

DROP TABLE student

GO



---->建表

create table student([Name] varchar(2),[Age] int,[Birthday] datetime)

insert student

select '李',12,'2010-09-10 09:42:37.000' union all

select '李',21,'2010-09-15 10:42:37.000' union all

select '李',25,'2010-09-01 12:43:37.000'

GO







--> 查询结果

SELECT * FROM student





select Name,SUM(Age) as Age ,CONVERT(char(7),Birthday,120)as Birthday from student

group by Name,CONVERT(char(7),Birthday,120)

--> 删除表格

--DROP TABLE student

select [name],

       sum(age),

       convert(varchar(7),Birthday,120)

from student

group by [name],convert(varchar(7),Birthday,120)

--> 测试数据: #tb

if object_id('tempdb.dbo.#tb') is not null drop table #tb

go

create table #tb (Name varchar(2),Age int,Birthday datetime)

insert into #tb

select '李',12,'2010-09-10 09:42:37.000' union all

select '李',21,'2010-09-15 10:42:37.000' union all

select '李',25,'2010-09-01 12:43:37.000'



select name,age=sum(age),Birthday =convert(varchar(7),Birthday ,120) 

from #tb 

group by name,convert(varchar(7),Birthday ,120)



name age         Birthday

---- ----------- --------

李    58          2010-09



(1 行受影响)