mssql CASE,GROUP BY用法

--create database dbTemp
use dbTemp
create table test
(
Pid int identity(1,1) not null primary key,
Years datetime,
IsFirstSixMonths int default(0), --0表示上半年1表示下半年--
TotalCome int
)
insert test
select 2007-1-1,0,50
union
select 2007-3-1,0,60
union
select 2007-12-1,1,80
union
select 2008-1-1,0,100
union
select 2008-12-1,1,100

select * from test

select convert(char(4),Years,120) as year,
IsFirstSixMonths=case when IsFirstSixMonths=0 then 上半年 when IsFirstSixMonths=1 then 下半年 END ,
sum(totalcome) as sum from test
group by IsFirstSixMonths,convert(char(4),Years,120)


select convert(char(4),Years,120) as year,
IsFirstSixMonths=case when IsFirstSixMonths=0 then 上半年 ELSE 下半年 END ,
sum(totalcome) as sum from test
group by IsFirstSixMonths,convert(char(4),Years,120)

--DROP DATABASE dbtemp

2007 上半年 110
2007 下半年 80
2008 上半年 100
2008 下半年 100