select
a.季度,
值 = isnull(sum(b.valuea),0)
from
(select 季度='一季度',month=1 union all
select 季度='一季度',month=2 union all
select 季度='一季度',month=3 union all
select 季度='二季度',month=4 union all
select 季度='二季度',month=5 union all
select 季度='二季度',month=6 union all
select 季度='三季度',month=7 union all
select 季度='三季度',month=8 union all
select 季度='三季度',month=9 union all
select 季度='四季度',month=10 union all
select 季度='四季度',month=11 union all
select 季度='四季度',month=12) a
left join
表 b
on
a.month = b.month
group by
a.季度
a.季度,
值 = isnull(sum(b.valuea),0)
from
(select 季度='一季度',month=1 union all
select 季度='一季度',month=2 union all
select 季度='一季度',month=3 union all
select 季度='二季度',month=4 union all
select 季度='二季度',month=5 union all
select 季度='二季度',month=6 union all
select 季度='三季度',month=7 union all
select 季度='三季度',month=8 union all
select 季度='三季度',month=9 union all
select 季度='四季度',month=10 union all
select 季度='四季度',month=11 union all
select 季度='四季度',month=12) a
left join
表 b
on
a.month = b.month
group by
a.季度
insert into tbl select 2005,1,1,1
insert into tbl select 2005,2,1,1
insert into tbl select 2005,4,1,1
insert into tbl select 2005,7,1,1
select
a.季度,
值 = isnull(sum(b.valuea),0)
from
(select 季度='一季度',id=1,month=1 union all
select 季度='一季度',id=1,month=2 union all
select 季度='一季度',id=1,month=3 union all
select 季度='二季度',id=2,month=4 union all
select 季度='二季度',id=2,month=5 union all
select 季度='二季度',id=2,month=6 union all
select 季度='三季度',id=3,month=7 union all
select 季度='三季度',id=3,month=8 union all
select 季度='三季度',id=3,month=9 union all
select 季度='四季度',id=4,month=10 union all
select 季度='四季度',id=4,month=11 union all
select 季度='四季度',id=4,month=12) a
left join
tbl b
on
a.month = b.month
group by
a.季度,a.id
order by
a.id/*
季度 值
---------------------------
一季度 2
二季度 1
三季度 1
四季度 0
*/drop table tbl
from
tbl a ,
(select '一季度' as name,1 as jidu
union all select '二季度' as name,2 as jidu
union all select '三季度' as name,3 as jidu
union all select '四季度' as name,4 as jidu
)b
where
b.jidu=
case
when a.month <4 then 1
when a.month <7 then 2
when a.month <10 then 3
else 4
end
group by b.name,year
insert into tbl select 2005,1,1,1
insert into tbl select 2005,2,1,1
insert into tbl select 2005,4,1,1
insert into tbl select 2005,7,1,1select b.name as '季度',值=count(month)
from
(select '一季度' as name,1 as jidu
union all select '二季度' as name,2 as jidu
union all select '三季度' as name,3 as jidu
union all select '四季度' as name,4 as jidu
)b left outer join
tbl a
on
b.jidu=
case
when a.month <4 then 1
when a.month <7 then 2
when a.month <10 then 3
else 4
end
group by b.name,year,b.jidu
order by b.jidu结果:
/*
季度 值
---------------------------
一季度 2
二季度 1
三季度 1
四季度 0
*/drop table tbl
结果:
-----------------------
季度 值
-----------------------
一季度 0.0
二季度 0.0
三季度 0.87700000000000022
四季度 0.0
-----------------------churchatp1(别看资料,看聊效!) 兄:语句少些,但结果用在我的表上就不对了。(使用您建的表就是对的)
结果:
-----------------------
季度 值
-----------------------
一季度 0
二季度 0
三季度 15
四季度 0
-----------------------
create table tbl(year int,month int,date int,valuea int)
insert into tbl select 2005,1,1,1
insert into tbl select 2005,2,1,1
insert into tbl select 2005,4,1,1
insert into tbl select 2005,7,1,1
--测试
Select N'一季度' As 季度,SUM(Case When month Between 1 And 3 Then valuea Else 0 End) As 值 from tbl
Union All
Select N'二季度',SUM(Case When month Between 4 And 6 Then valuea Else 0 End)from tbl
Union All
Select N'三季度',SUM(Case When month Between 7 And 9 Then valuea Else 0 End) from tbl
Union All
Select N'四季度',SUM(Case When month Between 10 And 12 Then valuea Else 0 End) from tbl
--删除测试环境
Drop Table tbl
--结果
/*
季度 值
一季度 2
二季度 1
三季度 1
四季度 0
*/