!!求助!! SQL 按季度分类查询
有如下表 Sale
syearsmonthsmoney
2011110
2011320
2011510
2011150
2011710
20111080
2011110
2011210
2011320
2011110
20118100
2011810
20111210
2012110
2012320
2012510
2012150
2012710
20121080
2012110
2012210
2012320
2012110
20128100
2012810
20121210
查出结果为
Syear第一季度第二季度第三季度第四季度
20111301012090
20121301012090
用一句SQL如何实现?
若不能,用存储过程也行 SQL?季度?统计?
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-19 17:15:07
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据:[Sale]
if object_id('[Sale]') is not null drop table [Sale]
go
create table [Sale]([syear] int,[smonth] int,[smoney] int)
insert [Sale]
select 2011,1,10 union all
select 2011,3,20 union all
select 2011,5,10 union all
select 2011,1,50 union all
select 2011,7,10 union all
select 2011,10,80 union all
select 2011,1,10 union all
select 2011,2,10 union all
select 2011,3,20 union all
select 2011,1,10 union all
select 2011,8,100 union all
select 2011,8,10 union all
select 2011,12,10 union all
select 2012,1,10 union all
select 2012,3,20 union all
select 2012,5,10 union all
select 2012,1,50 union all
select 2012,7,10 union all
select 2012,10,80 union all
select 2012,1,10 union all
select 2012,2,10 union all
select 2012,3,20 union ALL
select 2012,1,10 union all
select 2012,8,100 union all
select 2012,8,10 union all
select 2012,12,10
--------------开始查询--------------------------
select [syear],[第一季度]=SUM(CASE WHEN [smonth] BETWEEN 1 AND 3 THEN [smoney] ELSE 0 END ),
[第二季度]=SUM(CASE WHEN [smonth] BETWEEN 4 AND 6 THEN [smoney] ELSE 0 END ),
[第三季度]=SUM(CASE WHEN [smonth] BETWEEN 7 AND 9 THEN [smoney] ELSE 0 END ),
[第四季度]=SUM(CASE WHEN [smonth] BETWEEN 10 AND 12 THEN [smoney] ELSE 0 END )
from [Sale]
GROUP BY [syear]
----------------结果----------------------------
/*
syear 第一季度 第二季度 第三季度 第四季度
----------- ----------- ----------- ----------- -----------
2011 130 10 120 90
2012 130 10 120 90
*/