数据库的存储过程设计(1)_数据库存储过程设计

需求:

统计一个sql server表单中的故障数据，根据故障类型和故障等级及故障次数，统计出一段时间内的故障积分，要总分，及前三的故障分和故障类型。

Step1：

因为对db不太熟悉，并且觉得视图比较直观，可以看到中间结果，所以，第一步做的先是统计出相关故障和次数。这涉及到故障信息表，设备的信息表,故障的配置表，以及设备所在项目的配置表。因为故障配置和项目配置都有静默时间，在这段静默时间里的故障是不作数的，所以要剔除出去。

视图1 by_v_etor_fault_score

SELECT
a.id,
a.etor_id,
a.dtu_bianhao,
a.master_code,
a.create_time,
b.bianhao AS etor_bianhao,
b.dtu_id,
b.etor_species_id,
b.project_id,
b.organize_id,
b.dizhi_code,
b.build_name,
b.build_number,
b.build_etorindex,
b.dizhi_detail,
b.zuobiao,
b.zhuban_code,
b.zhuban_version,
b.zhuban_version_time,
b.floorcount,
b.weibao_user,
b.weibao_userphone,
b.weibao_manager,
b.weibao_managerphone,
b.weibao_interval,
b.weibao_type,
b.nianjian_time,
b.use_place,
b.brand_id,
b.etor_model,
b.product_model,
c.fault_code,
c.fault_display_code,
c.fault_desc,
c.is_alarm,
c.levels,
c.sleep_starttime,
c.sleep_endtime,
d.caption AS etor_species_caption,
d.code AS etor_species_code,
d.etor_type,
d.remote_aspx,
d.etor_default_img,
d.mask_format,
table_proj.sleep_starttime AS proj_sleep_starttime,
table_proj.sleep_endtime AS proj_sleep_endtime
 
 
FROM
dbo.by_etor_tickets_fault AS a
INNER JOIN dbo.by_etor AS b ON a.etor_id = b.id
INNER JOIN dbo.by_etor_fault_config AS c ON a.master_code = c.fault_code AND b.etor_species_id = c.etor_species_id
INNER JOIN dbo.by_etor_species AS d ON b.etor_species_id = d.id
INNER JOIN dbo.by_project AS table_proj ON b.project_id = table_proj.id
 
 
 
--filter sleep time 
where
		 (
			   ( ISNULL(table_proj.sleep_starttime, '') = ''  or ISNULL(table_proj.sleep_endtime, '') ='' )
				 OR
				 ( ISNULL(table_proj.sleep_starttime, '') != '' and ISNULL(table_proj.sleep_endtime, '') != ''
						AND 
						(
							(convert(datetime,table_proj.sleep_starttime) < convert(datetime,table_proj.sleep_endtime)
								AND convert(varchar(8),a.create_time,108) not between convert(datetime,table_proj.sleep_starttime) AND convert(datetime,table_proj.sleep_endtime)
							 )
							OR
								 (convert(datetime,table_proj.sleep_starttime) > convert(datetime,table_proj.sleep_endtime)
									AND (convert(varchar(8),a.create_time,108) between convert(datetime,table_proj.sleep_endtime) AND convert(datetime,table_proj.sleep_starttime))
							)
						)
					)
       )
       AND
		   (
				 ( ISNULL(c.sleep_starttime, '') = ''  or ISNULL(c.sleep_endtime, '') ='' )
				 OR
				 ( ISNULL(c.sleep_starttime, '') != '' and ISNULL(c.sleep_endtime, '') != ''
						AND 
						(
							(convert(datetime,c.sleep_starttime) < convert(datetime,c.sleep_endtime)
								AND convert(varchar(8),a.create_time,108) not between convert(datetime,c.sleep_starttime) AND convert(datetime,c.sleep_endtime)
							 )
							OR
								 (convert(datetime,c.sleep_starttime) > convert(datetime,c.sleep_endtime)
									AND (convert(varchar(8),a.create_time,108) between convert(datetime,c.sleep_endtime) AND convert(datetime,c.sleep_starttime))
							)
						)
					)
			 )
 
--end where ---

其结果如下

整合了4张表，类型比较多，或可筛选，当着不是我们当前关注的东西。

我们主要在于故障项。master_code栏是故障码，对应fault_code是一致的。由于一个设备可能会多次出现相同故障，以及根据levels划分的故障级别有不同的记分等级。所以基于上面我们有了第二个视图。

Step2：

对上个视图进行筛选，暂定于名称为:by_v_etor_fault_score_top3,因为我们最后在意的是前3个高分。

其中etor_id<100是只取前100个，只为在调试阶段减少数据量。

SELECT etor_id,fault_code,fault_desc,
										sum( case when levels=0  then 20 
															when levels=1  then 10 
															when levels=2  then 6 
															when levels=3  then 4 
															when levels=4  then 2 
															when levels=5  then 1   --0.1~1
													else 0 	end)  as fault_score 
from by_v_etor_fault_score 
where etor_id<100
GROUP BY etor_id,fault_code,fault_desc

其结果为，这儿并没有取前3，因为还要统计一个总分，但这个筛选语句明显少了，case when是故障等级相应的积分。

Step3:

针对上张表统计出的分数，进行最后整合需要。及取前三，和总分。

SELECT A.etor_id,fault_score_total,fault_code1,fault_score1,fault_des1,fault_code2,fault_score2,fault_des2,fault_code3,fault_score3,fault_des3 from
(
SELECT  etor_id,[1] as fault_score1, [2] as fault_score2, [3] as fault_score3
FROM
(
select a.etor_id,a.fault_score,a.fautl_score_range from (select t.*,row_number()over(partition by etor_id order by fault_score 
 
desc) as fautl_score_range from by_v_etor_fault_score_top3 t) a where fautl_score_range<=3 
)
 
AS SourceTable
 
PIVOT
(
 MAX(fault_score) FOR fautl_score_range IN ([1], [2], [3])
) AS PivotTable
)A
 
LEFT JOIN
 
(
SELECT  etor_id,[1] as fault_des1, [2] as fault_des2, [3] as fault_des3
FROM
(
	select a.etor_id,a.fault_desc,a.fautl_score_range from (select t.*,row_number()over(partition by etor_id order by fault_score 
 
	desc) as fautl_score_range from by_v_etor_fault_score_top3 t) a where fautl_score_range<=3 
)
AS SourceTable2
PIVOT
(
	MAX(fault_desc)	FOR fautl_score_range IN ([1], [2], [3])
 
) AS PivotTable2
)B on A.etor_id=B.etor_id
 
 
LEFT JOIN
 
(
SELECT  etor_id,[1] as fault_code1, [2] as fault_code2, [3] as fault_code3
FROM
(
	select a.etor_id,a.fault_code,a.fautl_score_range from (select t.*,row_number()over(partition by etor_id order by fault_score 
 
	desc) as fautl_score_range from by_v_etor_fault_score_top3 t) a where fautl_score_range<=3 
)
AS SourceTable2
PIVOT
(
	MAX(fault_code)	FOR fautl_score_range IN ([1], [2], [3])
 
) AS PivotTable2
)C on A.etor_id=C.etor_id
 
 
LEFT JOIN
 
(
SELECT  etor_id,sum(fault_score) as fault_score_total
FROM
 by_v_etor_fault_score_top3 
 
GROUP BY etor_id
)D on A.etor_id=D.etor_id

最终结果，这是便是我们要的结果

这只是我们用视图实现了我们要的结果，利用视图的直观性，中间有什么问题很方便调整，比如要添加设备的信息到最后表中应该也是很方便的。

后面我要把它设计成存储过程，因为其中还要涉及的故障发生的时间以便按月统计。