很久没写存储过程，今天写个存储下

 
GO
SET ANSI_NULLS ON
GO
if   exists   (select   *  from   dbo.sysobjects  
where   id=object_id(N'[dbo].[p_Police_Binding_Statistics]') 
and OBJECTPROPERTY(id,   N'IsProcedure') =  1)    
  drop   procedure   [dbo].[p_Police_Binding_Statistics]    
GO  
SET QUOTED_IDENTIFIER ON
GO
/*add 20190604 xqy 警员绑定统计*/
CREATE PROC [dbo].[p_Police_Binding_Statistics] 
    @OrgNo VARCHAR(40) , --组织机构代码
    @RoleId NVARCHAR(100)=null --角色ID
AS

DECLARE @R VARCHAR(12) ='';
SET @R=dbo.EarseZero(@OrgNO);

--二级组织机构集合 #t_Org
DECLARE @ID INT;
SELECT @ID=Id FROM  t_Organization WHERE OrgNo=@OrgNO AND IsDel=0
select dbo.EarseZero(OrgNo) as Org,OrgNo,ShowName as ShortName,OrgName
INTO #t_Org
FROM t_Organization WHERE (PId=@ID OR Id=@ID) AND IsDel=0
ORDER BY Org

--警员总数
select count(uid) as JYZS,GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N 
INTO #JYZS from T_user 
where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
and  (@OrgNo IS  null or  GAJGDM like ''+@R+'%') 
group by GAJGDM

--申请数
 select  count(PPID) as WeiXinTotal,u.GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N1 
 INTO #SQS 
 from t_policeprove p join t_user u  on p.policeid=u.Jh  
 where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
 and  (@OrgNo IS  null   or  GAJGDM like ''+@R+'%') 
group by GAJGDM

--实名认证数
 select  count(PPID) as Name_Authentication,u.GAJGDM,dbo.EarseZero(GAJGDM) as GAJGDM_N2  
 INTO #SMRZS
  from t_policeprove p join t_user u  on p.policeid=u.Jh 
 where (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
 and  (@OrgNo IS  null   or  GAJGDM like ''+@R+'%') 
and status=1 group by GAJGDM

--近7天访问总数

select count(ID) as Visit7Day,WorkUnit,dbo.EarseZero(WorkUnit) as GAJGDM_N3  
into #7TFWZS
from t_log  l join t_user u on l.UserId=u.UID   
 where DateDiff(dd,operationtime,getdate())<=7  
  and operationAction='150112' and  workUnit IS not null  
  and (@RoleId is null or uid in (select uid from t_userrole where RID=@RoleId))
  and  (@OrgNo IS  null   or  GAJGDM like ''+@R+'%') 
  group by WorkUnit

  --组织机构和警员总数联合
  select a.org,a.orgno,a.OrgName,sum(JYZS)JYZS  into  #Result1 from #t_Org a join #JYZS b on a.org=b.GAJGDM_N group by a.org,a.orgno,a.OrgName;
  --select * from #Result1;

   --组织机构和申请数，有疑问，多了
  select c.org,c.orgno,c.OrgName,sum(WeiXinTotal)WeiXinTotal  into  #Result2 from #t_Org c join #SQS d on c.org=d.GAJGDM_N1 group by c.org,c.orgno,c.OrgName;
  --select * from #Result2;

  --组织机构和实名认证数
  select e.org,e.orgno,e.OrgName,sum(Name_Authentication)Name_Authentication  into  #Result3 from #t_Org e join #SMRZS f on e.org=f.GAJGDM_N2 group by e.org,e.orgno,e.OrgName;
   --select * from #Result3;

   --组织机构和近七天访问数
  select g.org,g.orgno,g.OrgName,sum(Visit7Day)Visit7Day  into  #Result4 from #t_Org g join #7TFWZS h on g.org=h.GAJGDM_N3 group by g.org,g.orgno,g.OrgName;
  --select * from #Result4;
  
 
  --组织机构
  --JYZS 是警员总数
  --WeiXinTotal 是申请数
  --Name_Authentication 是实名认证数
  --WSQS 是未申请数 未申请数=申请数-实名认证数
  --Visit7Day 是近七天访问数

  select a1.*, 
  isnull(JYZS,0) JYZS,isnull(WeiXinTotal,0)WeiXinTotal,isnull(Name_Authentication,0)Name_Authentication,isnull(Visit7Day,0)Visit7Day
  --JYZS,WeiXinTotal,Name_Authentication,Visit7Day
  ,isnull(WeiXinTotal-Name_Authentication,0)WSQS
   From #t_Org a1 
  left join #Result1 b1 on a1.orgno=b1.orgno
  left join  #Result2 b2  on a1.orgno=b2.orgno
  left join  #Result3 b3  on a1.orgno=b3.orgno
  left join  #Result4 b4  on a1.orgno=b4.orgno