sql中sum的烦恼

strugglesun 2010-10-09 02:48:18
现在有张表student
select * from student
Name Age Birthday
李 12 2010-09-10 09:42:37.000
李 21 2010-09-15 10:42:37.000
李 25 2010-09-01 12:43:37.000


我现在用sum把Age字段相加起来,只显示一条结果:
Name Age Birthday
李 58 2010-09

这个sql语句应该怎么写啊?
...全文
37 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
strugglesun 2010-10-09
大家都是高手啊,结贴,给分
回复
王向飞 2010-10-09

--> 数据库版本:
--> 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

回复
水族杰纶 2010-10-09
select [name],
sum(age),
convert(varchar(7),Birthday,120)
from student
group by [name],convert(varchar(7),Birthday,120)
回复
SQL2088 2010-10-09
--> 测试数据: #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 行受影响)
回复
billpu 2010-10-09
这个生日还会变的呀...
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-09 02:48
社区公告
暂无公告