CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_audiothresholds_test`()
begin
declare disorderlow double(20,8);
declare disorderhigh double(20,8);
declare hitlow double(20,8);
declare hithigh double(20,8);
declare jitterlow double(20,8);
declare jitterhigh double(20,8);
declare plrlow double(20,8);
declare plrhigh double(20,8);
declare moslow double(20,8);
declare moshigh double(20,8);
declare count_audio int;
drop table if exists tempaudiothresholds;
create temporary table tempaudiothresholds(
disorder_low double(20,8) not null default 0,
disorder_high double(20,8) not null default 0,
hit_low double(20,8) not null default 0,
hit_high double(20,8) not null default 0,
jitter_low double(20,8) not null default 0,
jitter_high double(20,8) not null default 0,
plr_low double(20,8) not null default 0,
plr_high double(20,8) not null default 0,
mos_low double(20,8) not null default 0,
mos_high double(20,8) not null default 0
);
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='disorder';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @disorderlow from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='disorder';
set disorderlow=@disorderlow;
else
set disorderlow=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='disorder';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @disorderhigh from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='disorder';
set disorderhigh=@disorderhigh;
else
set disorderhigh=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='hit';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @hitlow from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='hit';
set hitlow=@hitlow;
else
set hitlow=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='hit';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @hithigh from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='hit';
set hithigh=@hithigh;
else
set hithigh=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='jitter';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @jitterlow from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='jitter';
set jitterlow=@jitterlow;
else
set jitterlow=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='jitter';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @jitterhigh from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='jitter';
set jitterhigh=@jitterhigh;
else
set jitterhigh=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='plr';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @plrlow from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='plr';
set plrlow=@plrlow;
else
set plrlow=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='plr';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @plrhigh from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='plr';
set plrhigh=@plrhigh;
else
set plrhigh=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='mos';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @moslow from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='low' and alarmtype_name='mos';
set moslow=@moslow;
else
set moslow=0;
end if;set count_audio=0;
select count(c.thresholds_value) into @count_audio from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='mos';
set count_audio=@count_audio;
if count_audio>0 then
select c.thresholds_value into @moshigh from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id and alarmlevel_name='high' and alarmtype_name='mos';
set moshigh=@moshigh;
else
set moshigh=0;
end if;set @test = concat("
insert into tempaudiothresholds(disorder_low,disorder_high,hit_low,hit_high,jitter_low,
jitter_high,plr_low,plr_high,mos_low,mos_high) values (",disorderlow,",",disorderhigh,",",hitlow,",",
hithigh,",",jitterlow,",",jitterhigh,",",plrlow,",",plrhigh,",",moslow,",",moshigh,");");
prepare s0 from @test;
execute s0;select * from tempaudiothresholds;
drop table if exists tempaudiothresholds;
end;
在存储过程中用空行分开的几段基本上大同小异,每次写一遍就做了遍重复功,那位大哥帮忙改下,谢谢!
DROP TABLE IF EXISTS `t_mediatype`;
CREATE TABLE `t_mediatype` (
`mediatype_id` int(11) NOT NULL DEFAULT '0',
`mediatype_name` varchar(40) NOT NULL DEFAULT 'N/A'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t_mediatype` VALUES (1,'video');
INSERT INTO `t_mediatype` VALUES (2,'audio');DROP TABLE IF EXISTS `t_alarmlevel`;
CREATE TABLE `t_alarmlevel` (
`alarmlevel_id` int(11) NOT NULL DEFAULT '0',
`alarmlevel_name` varchar(40) NOT NULL DEFAULT 'N/A',
`memo` varchar(200) DEFAULT 'N/A'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t_alarmlevel` VALUES (1,'low','N/A');
INSERT INTO `t_alarmlevel` VALUES (2,'high','N/A');
INSERT INTO `t_alarmlevel` VALUES (3,'avg','N/A');DROP TABLE IF EXISTS `t_alarmtype`;
CREATE TABLE `t_alarmtype` (
`alarmtype_id` int(11) NOT NULL DEFAULT '0',
`alarmtype_name` varchar(40) NOT NULL DEFAULT 'N/A',
`mediatype_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t_alarmtype` VALUES (1,'df',1);
INSERT INTO `t_alarmtype` VALUES (2,'mlr',1);
INSERT INTO `t_alarmtype` VALUES (3,'streamrate',1);
INSERT INTO `t_alarmtype` VALUES (4,'outage',1);
INSERT INTO `t_alarmtype` VALUES (5,'disorder',2);
INSERT INTO `t_alarmtype` VALUES (6,'hit',2);
INSERT INTO `t_alarmtype` VALUES (7,'jitter',2);
INSERT INTO `t_alarmtype` VALUES (8,'plr',2);
INSERT INTO `t_alarmtype` VALUES (9,'mos',2);DROP TABLE IF EXISTS `t_video_thresholds`;
CREATE TABLE `t_video_thresholds` (
`alarmtype_id` int(11) NOT NULL DEFAULT '0',
`alarmlevel_id` int(11) NOT NULL DEFAULT '0',
`thresholds_value` double(20,8) NOT NULL DEFAULT '0.00000000'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t_video_thresholds` VALUES (1,1,1);
INSERT INTO `t_video_thresholds` VALUES (1,2,2);
INSERT INTO `t_video_thresholds` VALUES (1,3,3);
INSERT INTO `t_video_thresholds` VALUES (2,1,4);
INSERT INTO `t_video_thresholds` VALUES (2,2,5);
INSERT INTO `t_video_thresholds` VALUES (2,3,6);
INSERT INTO `t_video_thresholds` VALUES (3,1,7);
INSERT INTO `t_video_thresholds` VALUES (3,2,8);
INSERT INTO `t_video_thresholds` VALUES (3,3,9);
INSERT INTO `t_video_thresholds` VALUES (4,1,10);
INSERT INTO `t_video_thresholds` VALUES (4,2,11);
INSERT INTO `t_video_thresholds` VALUES (4,3,12);
INSERT INTO `t_video_thresholds` VALUES (5,1,11);
INSERT INTO `t_video_thresholds` VALUES (5,2,12);
INSERT INTO `t_video_thresholds` VALUES (5,3,13);
INSERT INTO `t_video_thresholds` VALUES (6,1,14);
INSERT INTO `t_video_thresholds` VALUES (6,2,15);
INSERT INTO `t_video_thresholds` VALUES (6,3,16);
INSERT INTO `t_video_thresholds` VALUES (7,1,17);
INSERT INTO `t_video_thresholds` VALUES (7,2,18);
INSERT INTO `t_video_thresholds` VALUES (7,3,19);
INSERT INTO `t_video_thresholds` VALUES (8,1,20);
INSERT INTO `t_video_thresholds` VALUES (8,2,21);
INSERT INTO `t_video_thresholds` VALUES (8,3,22);
INSERT INTO `t_video_thresholds` VALUES (9,1,23);
INSERT INTO `t_video_thresholds` VALUES (9,2,24);
INSERT INTO `t_video_thresholds` VALUES (9,3,25);
begin
drop table if exists tempaudiothresholds;
create temporary table tempaudiothresholds(
disorder_low double(20,8) not null default 0,
disorder_high double(20,8) not null default 0,
hit_low double(20,8) not null default 0,
hit_high double(20,8) not null default 0,
jitter_low double(20,8) not null default 0,
jitter_high double(20,8) not null default 0,
plr_low double(20,8) not null default 0,
plr_high double(20,8) not null default 0,
mos_low double(20,8) not null default 0,
mos_high double(20,8) not null default 0
);
insert into tempaudiothresholds(disorder_low,disorder_high,hit_low,hit_high,jitter_low,jitter_high,plr_low,plr_high,mos_low,mos_high)
select
(case when a.alarmlevel_name='low' and b.alarmtype_name='disorder' then c.thresholds_value else 0 end) as disorderlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='disorder' then c.thresholds_value else 0 end) as disorderhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='hit' then c.thresholds_value else 0 end) as hitlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='hit' then c.thresholds_value else 0 end) as hithigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='jitter' then c.thresholds_value else 0 end) as jitterlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='jitter' then c.thresholds_value else 0 end) as jitterhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='plr' then c.thresholds_value else 0 end) as plrlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='plr' then c.thresholds_value else 0 end) as plrhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='mos' then c.thresholds_value else 0 end) as moslow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='mos' then c.thresholds_value else 0 end) as moshigh,
from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id;
select * from tempaudiothresholds;
drop table if exists tempaudiothresholds;
end;
CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_audiothresholds_test`()
begin
drop table if exists tempaudiothresholds;
create temporary table tempaudiothresholds(
disorder_low double(20,8) not null default 0,
disorder_high double(20,8) not null default 0,
hit_low double(20,8) not null default 0,
hit_high double(20,8) not null default 0,
jitter_low double(20,8) not null default 0,
jitter_high double(20,8) not null default 0,
plr_low double(20,8) not null default 0,
plr_high double(20,8) not null default 0,
mos_low double(20,8) not null default 0,
mos_high double(20,8) not null default 0
);
insert into tempaudiothresholds(disorder_low,disorder_high,hit_low,hit_high,jitter_low,jitter_high,plr_low,plr_high,mos_low,mos_high)
select
(case when a.alarmlevel_name='low' and b.alarmtype_name='disorder' then c.thresholds_value else 0 end) as disorderlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='disorder' then c.thresholds_value else 0 end) as disorderhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='hit' then c.thresholds_value else 0 end) as hitlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='hit' then c.thresholds_value else 0 end) as hithigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='jitter' then c.thresholds_value else 0 end) as jitterlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='jitter' then c.thresholds_value else 0 end) as jitterhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='plr' then c.thresholds_value else 0 end) as plrlow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='plr' then c.thresholds_value else 0 end) as plrhigh,
(case when a.alarmlevel_name='low' and b.alarmtype_name='mos' then c.thresholds_value else 0 end) as moslow,
(case when a.alarmlevel_name='high' and b.alarmtype_name='mos' then c.thresholds_value else 0 end) as moshigh
from t_alarmlevel a,t_alarmtype b,
t_video_thresholds c where c.alarmlevel_id=a.alarmlevel_id and
c.alarmtype_id=b.alarmtype_id;
select * from tempaudiothresholds;
drop table if exists tempaudiothresholds;
end;楼上的代码有个小错误,编译不过,具体是在as moshigh后面多了个逗号,改正之后经测试,效果不一样。在我最上面的罗嗦代码执行的结果集只有一条数据,而这个执行之后会有多条记录,并且数据会有一定规则;
虽然没出来我想要的效果,但是还是谢谢楼上的大哥,通过你的代码偷偷学了下mysql中case的用法,谢谢!
-> sum(if(alarmlevel_name='high' and alarmtype_name='disorder',c.thresholds_value,0)) as disorder_high,
-> sum(if(alarmlevel_name='low' and alarmtype_name='hit',c.thresholds_value,0)) as hit_low,
-> sum(if(alarmlevel_name='high' and alarmtype_name='hit',c.thresholds_value,0)) as hit_high,
-> sum(if(alarmlevel_name='low' and alarmtype_name='jitter',c.thresholds_value,0)) as jitter_low,
-> sum(if(alarmlevel_name='high' and alarmtype_name='jitter',c.thresholds_value,0)) as jitter_high,
-> sum(if(alarmlevel_name='low' and alarmtype_name='plr',c.thresholds_value,0)) as plr_low,
-> sum(if(alarmlevel_name='high' and alarmtype_name='plr',c.thresholds_value,0)) as plr_high,
-> sum(if(alarmlevel_name='low' and alarmtype_name='mos',c.thresholds_value,0)) as mos_low,
-> sum(if(alarmlevel_name='high' and alarmtype_name='mos',c.thresholds_value,0)) as mos_high
-> from t_alarmlevel a,t_alarmtype b,t_video_thresholds c
-> where c.alarmlevel_id=a.alarmlevel_id
-> and c.alarmtype_id=b.alarmtype_id
-> and (alarmlevel_name='high'
-> or alarmlevel_name='low'
-> )
-> and (
-> alarmtype_name='disorder'
-> or alarmtype_name='hit'
-> or alarmtype_name='jitter'
-> or alarmtype_name='plr'
-> or alarmtype_name='mos'
-> );
+--------------+---------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| disorder_low | disorder_high | hit_low | hit_high | jitter_low | jitter_high | plr_low | plr_high | mos_low | mos_high |
+--------------+---------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| 11.00000000 | 12.00000000 | 14.00000000 | 15.00000000 | 17.00000000 | 18.00000000 | 20.00000000 | 21.00000000 | 23.00000000 | 24.00000000 |
+--------------+---------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)mysql>