- ----------------------------
- --Author:旋风
- --Date:2008-09-11
- --Version:V1.0
- --Memo:SQL统计报表中1-12月的数据
- ----------------------------
- --> 测试数据: [Accident_Report]
- if object_id('[Accident_Report]') is not null drop table [Accident_Report]
- create table [Accident_Report] ([unitid] int,[DeathNumber] int,[GrievousNumber] int,[WoundNumber] int,[LoseMoney] int,[LoseDate] int,[Date] datetime)
- insert into [Accident_Report]
- select 1,3,2,4,5,1,'2008-3-3' union all
- select 1,3,2,4,5,1,'2008-3-8' union all
- select 1,5,7,4,5,1,'2008-3-5' union all
- select 5,3,2,4,5,1,'2008-3-4' union all
- select 4,3,2,4,5,1,'2008-8-3' union all
- select 2,7,1,6,5,3,'2008-8-5' union all
- select 2,7,1,6,5,3,'2008-8-3' union all
- select 3,4,2,4,9,5,'2008-9-3'
- --> 测试数据: [Systemmanagement_Department]
- if object_id('[Systemmanagement_Department]') is not null drop table [Systemmanagement_Department]
- create table [Systemmanagement_Department] ([UnitId] int,[UnitName] varchar(5))
- insert into [Systemmanagement_Department]
- select 1,'unit1' union all
- select 2,'unit2' union all
- select 3,'unit3' union all
- select 4,'unit4' union all
- select 5,'unit5'
- --select * from [Accident_Report]
- --select * from [Systemmanagement_Department]
- SELECT
- SUM(isnull(a.DeathNumber,0)) AS M1,
- SUM(isnull(a.GrievousNumber,0)) AS M2,
- SUM(isnull(a.WoundNumber,0)) AS M3,
- SUM(isnull(a.LoseMoney,0)) AS M4,
- SUM(isnull(a.LoseDate,0)) AS M5,
- COUNT(*) AS M9,
- SUM(isnull(DeathNumber,0)) AS qq,
- MONTH(a.Date) AS Month,
- STR(MONTH(a.Date)) + '月' AS strMonth,
- YEAR(a.Date) AS Year,
- STR(YEAR(a.Date)) + '年' AS strYear,
- a.UnitId,b.UnitName,
- a.tmonth
- FROM
- (
- select * from [Accident_Report] m right join
- (
- select '1' as tmonth union all select '2' union all select '3' union all
- select '4' union all select '5' union all select '6' union all
- select '7' union all select '8' union all select '9' union all
- select '10' union all select '11' union all select '12'
- ) n --月份临时表
- on MONTH(m.Date)=n.tmonth
- ) a
- LEFT JOIN Systemmanagement_Department b
- ON a.UnitId = b.UnitId
- GROUP BY a.tmonth,month(a.date), a.UnitId,
- b.UnitName, YEAR(a.Date)
- --删除测试
- drop table Accident_Report
- drop table Systemmanagement_Department
- /*
- M1 M2 M3 M4 M5 M9 qq Month strMonth Year strYear UnitId UnitName tmonth
- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------ ----------- ------------ ----------- -------- ------
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 1
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 10
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 11
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 12
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 2
- 11 11 12 15 3 3 11 3 3月 2008 2008年 1 unit1 3
- 3 2 4 5 1 1 3 3 3月 2008 2008年 5 unit5 3
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 4
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 5
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 6
- 0 0 0 0 0 1 0 NULL NULL NULL NULL NULL NULL 7
- 14 2 12 10 6 2 14 8 8月 2008 2008年 2 unit2 8
- 3 2 4 5 1 1 3 8 8月 2008 2008年 4 unit4 8
- 4 2 4 9 5 1 4 9 9月 2008 2008年 3 unit3 9
- */
SQL统计报表中1-12月的数据
最新推荐文章于 2023-03-13 17:21:10 发布