!!求助!! SQL 按季度分类查询

jarydon 2013-08-19 05:12:44

有如下表 Sale
syear smonth smoney
2011 1 10
2011 3 20
2011 5 10
2011 1 50
2011 7 10
2011 10 80
2011 1 10
2011 2 10
2011 3 20
2011 1 10
2011 8 100
2011 8 10
2011 12 10
2012 1 10
2012 3 20
2012 5 10
2012 1 50
2012 7 10
2012 10 80
2012 1 10
2012 2 10
2012 3 20
2012 1 10
2012 8 100
2012 8 10
2012 12 10

查出结果为
Syear 第一季度第二季度第三季度第四季度
2011 130 10 120 90
2012 130 10 120 90

用一句SQL如何实现?
若不能,用存储过程也行

...全文

75 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

發糞塗牆 2013-08-19

打赏
举报

----------------------------------------------------------------

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

*/