年度，季度，月份

们在处理设计到日期统计时候经常会按照年度，季度，月份统计进行同期比或者环比先看看表结构 Sql代码 /****** 对象: Table [dbo].[t_case_statistics] 作者：JC_Dreaming 脚本日期: 08/30/2010 11:53:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[t_case_statistics]( [caseid] [bigint] IDENTITY(1,1) NOT NULL, [caseapplicant] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL, [casestartdate] [datetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF 其中caseid 主键自增长，caseapplicant为申报人，casestartdate起始时间。就按照起始时间进行统计。插入几条测试语句 Sql代码 insert into t_case_statistics (caseapplicant,casestartdate) values ('权限以上企业投资备案项目上报',convert(DATETIME,'2009-08-28 13:14:56',111)); insert into t_case_statistics (caseapplicant,casestartdate) values ('一、二类机动车维修经',convert(DATETIME,'2009-08-28 14:11:09',111)); insert into t_case_statistics (caseapplicant,casestartdate) values ('盟工商非公司企业',convert(DATETIME,'2009-08-28 14:16:10',111)); insert into t_case_statistics (caseapplicant,casestartdate) values ('利用外国政府贷款',convert(DATETIME,'2010-08-27 15:13:12',111)); ...... 呵呵，我们看看数据库组织语句 Sql代码 select years as '年份', case when months=1 then counts else 0 end '1月份', case when months=2 then counts else 0 end '2月份', case when months=3 then counts else 0 end '3月份', case when quarters=1 then counts else 0 end '一季度', case when months=4 then counts else 0 end '4月份', case when months=5 then counts else 0 end '5月份', case when months=6 then counts else 0 end '6月份', case when quarters=2 then counts else 0 end '二季度', case when months=7 then counts else 0 end '7月份', case when months=8 then counts else 0 end '8月份', case when months=9 then counts else 0 end '9月份', case when quarters=3 then counts else 0 end '三季度', case when months=10 then counts else 0 end '10月份', case when months=11 then counts else 0 end '11月份', case when months=12 then counts else 0 end '12月份', case when quarters=4 then counts else 0 end '四季度' from( select datepart(yy,caseStartDate) as years,datepart(q,caseStartDate) as quarters, datepart(mm,caseStartDate) months,count(1)as counts from ximeng_web.dbo.t_case_statistics group by year(caseStartDate),datepart(q,caseStartDate),month(caseStartDate) )as test 其中month(caseStartDate) 等效于datepart(mm,caseStartDate) year(caseStartDate)等效于 datepart(yy,caseStartDate) 可惜sql只提供了这两个统计函数，需要按照其它日期统计就不如datepart()灵活方便自如