最近在项目中需要做一些复杂的图表统计，所以使用了存储过程和游标，案例如下：
ALTER PROCEDURE [dbo].[proc_CountSiteGroupByAreaSiteType]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare
@Com_SiteTypeID as uniqueidentifier,
@SiteTypeName as nvarchar(Max),
@Com_StructureID as uniqueidentifier,
@Name as nvarchar(Max)
--创建临时表
create table #temp(
Name nvarchar(Max),
SiteTypeName nvarchar(Max),
Count int
)
declare C_SiteType Cursor for
select Com_SiteTypeID,SiteTypeName from Com_SiteType --声明游标
open C_SiteType --打开游标
FETCH NEXT FROM C_SiteType into @Com_SiteTypeID,@SiteTypeName
while @@FETCH_STATUS =0 --循环第一层游标的返回值状态
begin
declare C_Structure Cursor for
select Com_StructureID,Name from Com_Structure
where Com_StructureID in (select a.Com_StructureID from Com_Structure a inner join
Com_StructureType b on a.Com_SructureTypeID=b.Com_StructureTypeID and b.Type=3
and a.ParentStructureID!='00000000-0000-0000-0000-000000000000')--type=1表示只统计片区的项目
open C_Structure
FETCH NEXT FROM C_Structure into @Com_StructureID,@Name
while @@FETCH_STATUS =0 循环第二层游标的返回值状态
begin
--select Name=@Name,SiteTypeName=@SiteTypeName,Count(*) as Count
--into #temp from Com_SiteGroup
--where Com_StructureID=@Com_StructureID and Com_SiteTypeID=@Com_SiteTypeID
insert into #temp select Name=@Name,SiteTypeName=@SiteTypeName,Count(*) as Count
from Com_SiteGroup a inner join Com_SiteGroupInStructure b
on a.Com_SiteGroupID=b.Com_SiteGroupID
where b.Com_StructureID=@Com_StructureID and Com_SiteTypeID=@Com_SiteTypeID
and EngineeringState=1 --EngineeringState=1表示只统计在建的项目
FETCH NEXT FROM C_Structure into @Com_StructureID,@Name
end
close C_Structure
DEALLOCATE C_Structure --关闭内层游标，并撤销，才能继续正常执行外层游标，如果不关闭，会使外层游标返回值不为0
FETCH NEXT FROM C_SiteType into @Com_SiteTypeID,@SiteTypeName
end
close C_SiteType --关闭游标
DEALLOCATE C_SiteType --撤销游标
--select * from #temp
---pivot 行转列
DECLARE @sql VARCHAR(8000)
SELECT @sql=isnull(@sql+',','')+SiteTypeName FROM #temp GROUP BY SiteTypeName
SET @sql='select * from #temp pivot (Sum(Count) for SiteTypeName in ('+@sql+'))a'
exec(@sql)
drop table #temp
END