|
USE [tempdb]
GO
/****** 对象:? StoredProcedure [dbo].[sp_sum]??? 脚本日期: 04/26/2013 14:36:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:??<Author,Name>
-- Create date: <Create Date,>
-- Description:?<Description,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_sum]
?-- Add the parameters for the stored procedure here
AS
?declare @iii int
BEGIN
?-- SET NOCOUNT ON added to prevent extra result sets from
?-- interfering with SELECT statements.
?-- SET NOCOUNT ON
?
??? -- Insert statements for procedure here
?--第一步：把数据纳入到表tb_jhsy 手工从excle插入到表
?--第二步:把数据插入到表tb_sum
?delete from tb_sum
?declare @result VARCHAR(30)
?Declare curSF Cursor for??
??? select distinct xs from tb_jhsy;---查询语句（查询所有用户存储过程名称）?
?Open curSF??
? --循环并提取记录??
? Fetch Next From curSF Into @result--取第一条记录存入@result中??
? While ( @@Fetch_Status=0 )????
??????? begin?
???insert into tb_sum(dz,cb,qy,yf,yhyf)
????select @result,b.cb,sum(b.qy),sum(b.yf),sum(b.yfy) from (
????select a.cb,count(a.cb) as qy,0 as yf,0 as yfy from tb_jhsy a
????where a.xs = @result? group by a.cb
????union all
????select a.cb,0 as qy,count(a.cb) as yf,0 as yfy from tb_jhsy a
????where a.xs = @result and a.lb like '全员育妇%' group by a.cb
????union all
????select a.cb,count(a.cb) as yfy from tb_jhsy a
????where a.xs = @result and a.lb like '全员育妇已%' group by a.cb
????) as b
????group by b.cb
?????????? Fetch Next From curSF into @result ----下一条??
??????? end??
? --关闭游标?????
?? Close curSF?
? --释放游标??
?Deallocate curSF??
?--第三步:统计出需要的数据
??? declare @s varchar(8000)
?set @s='select cb'
?select @s=@s+',['+ltrim(rtrim(dz))+'qy]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then qy else 0 end)'+',['+ltrim(rtrim(dz))+'yf]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then yf else 0 end)'+',['+ltrim(rtrim(dz))+'yhyf]=sum(case ltrim(rtrim(dz)) when '''+ltrim(rtrim(dz))+''' then yhyf else 0 end )'
?from tb_sum
?group by dz
?set @s=@s+' from tb_sum group by cb '
?exec(@s)
END
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
