sql PivotTable

最新推荐文章于 2024-04-24 17:16:13 发布

tangleisoft

最新推荐文章于 2024-04-24 17:16:13 发布

阅读量444

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/tangleisoft/article/details/84808700

本文展示了一段SQL脚本，该脚本用于从Person.Person表中按年份和类型统计不同人员的数量，并计算每年的总数量。通过游标遍历每一年的数据，实现了对历史数据的高效统计。

declare @year int;
declare @sum int;
truncate table dbo.temp

declare cur Cursor
for
select distinct year(ModifiedDate) from Person.Person order by year(ModifiedDate) asc ;

open cur;
fetch next from cur into @year
while @@FETCH_STATUS=0
begin
print @year
insert into dbo.temp select year(ModifiedDate) ,PersonType,count(BusinessEntityID) from Person.Person group by year(ModifiedDate),PersonType having year(ModifiedDate)=@year ;
set @sum=0
select @sum=count(BusinessEntityID) from person.Person where year(ModifiedDate)=@year
insert into dbo.temp values(null,convert(nvarchar(10),@year), @sum)
fetch next from cur into @year
end
select * from dbo.temp
close cur
DEALLOCATE cur

years persontyoe counts
2006 EM 1
2006 GC 1
NULL 2006 2
2007 EM 8
2007 GC 8
NULL 2007 16
2008 EM 83
2008 GC 83
NULL 2008 166
2009 EM 135
2009 GC 135
NULL 2009 270
2010 EM 36
2010 SP 1
2010 GC 36
NULL 2010 73
2011 EM 5
2011 IN 1201
2011 SP 9
2011 SC 238
2011 VC 63
2011 GC 14
NULL 2011 1530
2012 IN 2743
2012 SP 4
2012 SC 250
2012 VC 93
2012 GC 4
NULL 2012 3094
2013 EM 3
2013 IN 8522
2013 SP 3
2013 SC 251
2013 GC 6
NULL 2013 8785
2014 EM 2
2014 IN 6018
2014 SC 4
2014 GC 2
NULL 2014 6026
2015 SC 10
NULL 2015 10