按月份统计汇总排序代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】按月份统计汇总排序
作者:Dezai.CN / 发布于2012/8/23/ 733
按月份统计汇总排序
<div>CREATE TABLE [dbo].[Bhjd_Joint](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Month] [datetime] NULL,
[Point] [decimal](18, 2) NULL,
[SelOrgID] [varchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[OrgID] [varchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[AddUserID] [varchar](40) COLLATE Chinese_PRC_CI_AS NULL,
[AddDate] [datetime] NULL
);
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (1, N'AAAA', CAST(0x00009EB800000000 AS DateTime), CAST(30.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F510153181B AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (2, N'BBB', CAST(0x00009EB800000000 AS DateTime), CAST(31.00 AS Decimal(18, 2)), N'80ebe14b-50f3-4e31-807a-e7e93edab60b', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F5101544D30 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (3, N'ab', CAST(0x00009F1300000000 AS DateTime), CAST(10.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'e91763db-0573-49fe-8ab5-97f2ec8915b2', N'173f89e3-4eab-4bb1-a0a4-162fd6b20908', CAST(0x00009F5300ED2C4F AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (4, N'AAAA', CAST(0x00009ED600000000 AS DateTime), CAST(30.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F51015317D0 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (5, N'BBB', CAST(0x00009ED600000000 AS DateTime), CAST(31.00 AS Decimal(18, 2)), N'80ebe14b-50f3-4e31-807a-e7e93edab60b', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F5101544D30 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (6, N'a', CAST(0x00009ED600000000 AS DateTime), CAST(10.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'e91763db-0573-49fe-8ab5-97f2ec8915b2', N'173f89e3-4eab-4bb1-a0a4-162fd6b20908', CAST(0x00009F5300ED2C04 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (7, N'AAAA', CAST(0x00009EF500000000 AS DateTime), CAST(30.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F51015317D0 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (8, N'BBB', CAST(0x00009F1300000000 AS DateTime), CAST(31.00 AS Decimal(18, 2)), N'80ebe14b-50f3-4e31-807a-e7e93edab60b', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F5101544D30 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (9, N'a', CAST(0x00009F3200000000 AS DateTime), CAST(10.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'e91763db-0573-49fe-8ab5-97f2ec8915b2', N'173f89e3-4eab-4bb1-a0a4-162fd6b20908', CAST(0x00009F5300ED2C04 AS DateTime))
INSERT [dbo].[Bhjd_Joint] ([Id], [Name], [Month], [Point], [SelOrgID], [OrgID], [AddUserID], [AddDate]) VALUES (10, N'AAAA', CAST(0x00009EF500000000 AS DateTime), CAST(30.00 AS Decimal(18, 2)), N'3e6b57a6-5d76-4a9f-a99b-42c300a33c6a', N'301d9d2d-8ce9-4b62-805d-42263d9f8646', N'1c4813e9-7fc2-4cc8-8a7e-2b5bea2b6de9', CAST(0x00009F51015317D0 AS DateTime))
</div>
<div></div>
<div>use [TempDB]</div>
<div>select Org.[Name] as [社区],a.* from dbo.Organization org,
(</div>
<div>select [Name] as [姓名],year([Month]) as [年份],[selorgId],sum(Point) as [总数],
 sum(case month([Month]) when 1 then Point else 0 end) [1月份],
 sum(case month([Month]) when 2 then Point else 0 end) [2月份],
 sum(case month([Month]) when 3 then Point else 0 end) [3月份],
 sum(case month([Month]) when 4 then Point else 0 end) [4月份],
sum(case month([Month]) when 5 then Point else 0 end) [5月份],
sum(case month([Month]) when 6 then Point else 0 end) [6月份],
sum(case month([Month]) when 7 then Point else 0 end) [7月份],
sum(case month([Month]) when 8 then Point else 0 end) [8月份],
sum(case month([Month]) when 9 then Point else 0 end) [9月份],
sum(case month([Month]) when 10 then Point else 0 end) [10月份],
sum(case month([Month]) when 11 then Point else 0 end) [11月份],
 sum(case month([Month]) when 12 then Point else 0 end) [12月份]
from dbo.Bhjd_Joint
where year([Month])=2011
group by [Name],year([Month]),[selorgId]</div>
<div>) a 
where a.selorgid=org.Id
order by [总数] desc</div>
<div></div>
<div></div>
试试其它关键字
　按月份统计汇总排序　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)