复制代码 代码如下:

--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