日期:2014-05-17 浏览次数:20440 次
declare @test table(id int, name nvarchar(4),score1 int, score2 int, score3 int, dt datetime)
insert into @test
select 123, N'张三', 1, 1, 1, '2012-05-01' union
select 123, N'张三', 1, 1, 1, '2012-05-03' union
select 123, N'张三', 1, 1, 1, '2012-05-12' union
select 123, N'张三', 1, 1, 1, '2012-05-24' union
select 123, N'张三', 1, 1, 1, '2012-05-28' union
select 123, N'张三', 1, 1, 1, '2012-05-31' union
select 123, N'李四', 1, 1, 1, '2012-06-01' union
select 123, N'李四', 2, 2, 2, '2012-06-13' union
select 123, N'李四', 1, 2, 1, '2012-06-12' union
select 123, N'李四', 1, 2, 2, '2012-06-15' union
select 123, N'李四', 1, 1, 2, '2012-06-22' union
select 123, N'李四', 1, 2, 1, '2012-06-30'
;with t as
(
select *,case when day(dt) between 1 and 10 then N'上旬' when day(dt) between 11 and 20 then N'中旬' else N'下旬' end dt_range from @test
)
select id,name,sum(score1) score1,sum(score2) score2,sum(score3) score3,convert(varchar(7),dt,120) yearMonth,dt_range from t
group by id,name,convert(varchar(7),dt,120),dt_range
/*
id