sql 按月份统计汇总排序

关注

原创最新推荐文章于 2026-03-03 08:39:18 发布 7.8k 阅读 AI 写同款· GEO 优化 ›

Sql面试题-按月份统计用户访问表结构： CREATE TABLE `action` ( userId varchar(25) DEFAULT NULL comment '用户id', visitDate varchar(25) DEFAULT NULL comment '浏览日期', visitCount int(11) DEFAULT NULL comment '浏览次数' ) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = 阅读详情

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

use [TempDB]

select Org.[Name] as [社区],a.* from dbo.Organization org,
(

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]

) a 
where a.selorgid=org.Id
order by [总数] desc

收录于