sqlservser,linq实现按月份统计

sqlserver实现月份统计

表结构

 

select convert(varchar(7),AccessDateTime,120) as Date,

sum(AccessCount) as [Count] from Counter

group by convert(varchar(7),AccessDateTime,120)

 

这种写法只会查询出有记录的月份，没记录的月份不会显示，

如下方法：

declare @Year int
set @Year=2021

select m as [Date],
sum(
　　case when datepart(month,AccessDateTime)=m
　　then AccessCount else 0 end
) as [Count] from Counter c,
(
　　select 1 m
　　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
) aa
where @Year=year(AccessDateTime) group by m

 

效果:

 

linq实现月份统计

var objectList = DbQuery.Query<Product>();

var list = from s in objectList

where s.CreateTime.Year == 2021
group s by s.CreateTime.Month into g
select new StatisticsObject
{
Dates = g.Key.ToString() + "月",
Count = g.Count()
};

 

效果:

 

同样这种写法只会查询出有记录的月份，没记录的月份不会显示

linq写法显示没记录的月份我还没研究出来,哪位大神有方法可以在评论区交流一下

 

转载于： https://blog.csdn.net/weixin_34174789/article/details/112952193