sql 按照日期月份统计

非常巧妙的ＳＱＬ统计，作者（嘿嘿，不是我哦）Jarry


SQL统计,按月分组统计,有时候有些月份没有数据, =============================================
-- 功能: 年度个人社保缴费清单
-- 作者: Jarry
-- 完成日期: 2008.3.13
-- =============================================
CREATE PROCEDURE [dbo].[proc_bg_SecurityYearPersonReport]
(
@Year nvarchar(4),
@StaffID int
)
AS
BEGIN
SELECT
(CONVERT(varchar,mMonth)+'月') AS 'Month',
b.CompanyPension,b.PersonPension,b.CompanyMedical,
b.PersonMedical,b.Maternity,b.WorkInjury,b.Unemployment
FROM
(SELECT 1 AS 'mMonth'
UNION SELECT 2 AS 'mMonth'
UNION SELECT 3 AS 'mMonth'
UNION SELECT 4 AS 'mMonth'
UNION SELECT 5 AS 'mMonth'
UNION SELECT 6 AS 'mMonth'
UNION SELECT 7 AS 'mMonth'
UNION SELECT 8 AS 'mMonth'
UNION SELECT 9 AS 'mMonth'
UNION SELECT 10 AS 'mMonth'
UNION SELECT 11 AS 'mMonth'
UNION SELECT 12 AS 'mMonth') m
LEFT JOIN
(
SELECT PayDate,sum(CompanyPension) CompanyPension,
sum(PersonPension) PersonPension,sum(CompanyMedical) CompanyMedical,
sum(PersonMedical) PersonMedical,sum(Maternity) Maternity,
sum(WorkInjury) WorkInjury,
sum(Unemployment) Unemployment FROM TB_Insurance
WHERE staID=@StaffID AND substring(cast(PayDate as nvarchar(6)),0,5)=@Year
GROUP BY PayDate
) b
on m.mMonth=convert(int,substring(convert(varchar,b.payDate),5,7))
END
GO
结果:

1月 NULL NULL NULL NULL NULL NULL NULL
2月 2345.00 236.00 2345.00 .00 .00 234.00 2345.00
3月 420.00 420.00 420.00 420.00 420.00 420.00 420.00
4月 NULL NULL NULL NULL NULL NULL NULL
5月 NULL NULL NULL NULL NULL NULL NULL
6月 NULL NULL NULL NULL NULL NULL NULL
7月 NULL NULL NULL NULL NULL NULL NULL
8月 .00 .00 .00 .00 .00 .00 .00
9月 NULL NULL NULL NULL NULL NULL NULL
10月 NULL NULL NULL NULL NULL NULL NULL
11月 NULL NULL NULL NULL NULL NULL NULL
12月 NULL NULL NULL NULL NULL NULL NULL


=================================================================================

-- =============================================
-- 功能: 年度社保费缴交人数及金额一览表
-- 作者: Jarry
-- 完成日期: 2008.3.13
-- =============================================
CREATE PROCEDURE [dbo].[proc_bg_SecurityYearCountReport]
(
@Year nvarchar(4)
)
AS
BEGIN
SELECT
(CONVERT(varchar,mMonth)+'月') AS 'Month',
b.Pension,b.PensionCount,b.Medical,
b.MedicalCount,b.Maternity,b.MaternityCount,
b.WorkInjury,b.WorkInjuryCount,b.Unemployment,b.UnemploymentCount
FROM
(SELECT 1 AS 'mMonth'
UNION SELECT 2 AS 'mMonth'
UNION SELECT 3 AS 'mMonth'
UNION SELECT 4 AS 'mMonth'
UNION SELECT 5 AS 'mMonth'
UNION SELECT 6 AS 'mMonth'
UNION SELECT 7 AS 'mMonth'
UNION SELECT 8 AS 'mMonth'
UNION SELECT 9 AS 'mMonth'
UNION SELECT 10 AS 'mMonth'
UNION SELECT 11 AS 'mMonth'
UNION SELECT 12 AS 'mMonth') m
LEFT JOIN
(
SELECT PayDate,sum(CompanyPension)+sum(PersonPension) Pension,
sum(case when CompanyPension+PersonPension>0 then 1 else 0 end) PensionCount,
sum(CompanyMedical)+sum(PersonMedical) Medical,
sum(case when CompanyMedical+PersonMedical>0 then 1 else 0 end) MedicalCount,
sum(Maternity) Maternity,
sum(case when Maternity>0 then 1 else 0 end) MaternityCount,
sum(WorkInjury) WorkInjury,
sum(case when WorkInjury>0 then 1 else 0 end) WorkInjuryCount,
sum(Unemployment) Unemployment,
sum(case when Unemployment>0 then 1 else 0 end) UnemploymentCount
FROM TB_Insurance
where substring(cast(PayDate as nvarchar(6)),0,5)=@Year
GROUP BY PayDate
) b
on m.mMonth=convert(int,substring(convert(varchar,b.payDate),5,7))
END
GO
达到如下效果

月 份 养 老 工 伤 医 疗 失 业 生 育
金 额 人 数 金 额 人 数 金 额 人 数 金 额 人 数 金 额 人 数
一月
二月
三月
四月
五月 　
六月
七月
八月
九月
十月
十一月
十二月

注:此处日期字段是int型:200808,200810