首页 新闻 会员 周边 捐助

请问这个有人看的懂吗 看得懂的能详细解释一下吗

0
悬赏园豆:10 [已解决问题] 解决于 2015-06-15 11:04

/* exec p_EquipmentRegisterAndUseStatistic */ --统计某地区某时间段内的核发和注销的设备(备案和使用)数量 alter proc p_EquipmentRegisterAndUseStatistic @cityid int=null--城市id ,@areaid int=null--区县id ,@datestart nvarchar(10)=null--起始日期 ,@dateend nvarchar(10)=null--截止日期 as

--所有的设备类型 select distinct EquipmentType into #equipTypes from dbo.EquipmentRecordApplication union all select '合计' as EquipmentType

--每种类型登记的数量 select EquipmentType ,count(1) as RegisterCount into #regcnt from dbo.EquipmentRecordApplication a join dbo.WorkflowRecords b on b.EquipmentID =a.ID and b.WorkflowCode='W0101' and b.StatusCode='S05' where a.IsActive=1 group by EquipmentType union all select '合计' as EquipmentType ,count(1) as RegisterCount from dbo.EquipmentRecordApplication a join dbo.WorkflowRecords b on b.EquipmentID =a.ID and b.WorkflowCode='W0101' and b.StatusCode='S05' where a.IsActive=1

--每种类型注销登记的数量

--每种类型使用申请的数量 select b.EquipmentType ,COUNT(1) as UseCount into #usecnt from dbo.UseApplyInfo a join dbo.EquipmentRecordApplication b on b.ID = a.EquipmentID and b.IsActive=1 join dbo.WorkflowRecords c on c.EquipmentID =a.EquipmentID and c.WorkflowCode='W0203' and c.StatusCode='S05' where a.IsActive=1 group by b.EquipmentType union all select '合计' as EquipmentType ,count(1) as UseCount from dbo.UseApplyInfo a join dbo.EquipmentRecordApplication b on b.ID = a.EquipmentID and b.IsActive=1 join dbo.WorkflowRecords c on c.EquipmentID =a.EquipmentID and c.WorkflowCode='W0203' and c.StatusCode='S05' where a.IsActive=1

--每种类型注销使用的数量

--结果 select a.EquipmentType ,b.RegisterCount ,c.UseCount from #equipTypes a left join #regcnt b on b.EquipmentType=a.EquipmentType left join #usecnt c on c.EquipmentType = a.EquipmentType

drop table #equipTypes,#regcnt,#usecnt

.net小菜鸟的主页 .net小菜鸟 | 初学一级 | 园豆:187
提问于:2015-06-09 15:43
< >
分享
最佳答案
0

这个不是很复杂,贴出来前请先格式化一下

收获园豆:5
| 菜鸟二级 |园豆:380 | 2015-06-09 18:16
其他回答(2)
0

存储过程加临时表啊。

收获园豆:5
最初的、天空 | 园豆:40 (初学一级) | 2015-06-09 16:42

能详细点吗 ?

支持(0) 反对(0) .net小菜鸟 | 园豆:187 (初学一级) | 2015-06-09 16:45
0

看来只能靠自己了

.net小菜鸟 | 园豆:187 (初学一级) | 2015-06-15 11:03
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册