二话不说,先贴代码CREATE PROCEDURE SP_Ping_Dialtest (iGood int,iStandard int)
BEGIN
select NETWORK_TYPE,
count(*) as C,
sum(case when CONNECTION_status='1' then 1 else 0 end) as D,
(sum(case when CONNECTION_status='1' then 1 else 0 end) / COUNT(*)) as chengg,
max(time_lapse),
min(time_lapse),
(sum(time_lapse)/count(*)) as X ,
sum(case when time_lapse >=iGood then 1 else 0 end)/count(*) as youxiu,
sum(case when time_lapse >=iStandard and time_lapse <iGood then 1 else 0 end)/count(*) as youxiu1,
sum(case when time_lapse <iStandard then 1 else 0 end)/count(*) as youxiu2 -- sum(case when time_lapse >=8 then 1 else 0 end)/count(*) as youxiu,
-- sum(case when time_lapse >=6 and time_lapse <8 then 1 else 0 end)/count(*) as youxiu1,
-- sum(case when time_lapse <6 then 1 else 0 end)/count(*) as youxiu2
from ping_dialtest_back
group By NETWORK_TYPE
end
从表里查处数据,带2个参数。。如果不是存储过程,直接执行没有问题。但是做成存储过程后,执行错误。提示在
select NETWORK_TYPE,
count(*) as C,
这里就发生错误了。把查询的字段一行写一个,这样看起来很清爽舒服。但是不知道为什么创建的时候会失败。请高手帮忙看下。。
BEGIN
select NETWORK_TYPE,
count(*) as C,
sum(case when CONNECTION_status='1' then 1 else 0 end) as D,
(sum(case when CONNECTION_status='1' then 1 else 0 end) / COUNT(*)) as chengg,
max(time_lapse),
min(time_lapse),
(sum(time_lapse)/count(*)) as X ,
sum(case when time_lapse >=iGood then 1 else 0 end)/count(*) as youxiu,
sum(case when time_lapse >=iStandard and time_lapse <iGood then 1 else 0 end)/count(*) as youxiu1,
sum(case when time_lapse <iStandard then 1 else 0 end)/count(*) as youxiu2 -- sum(case when time_lapse >=8 then 1 else 0 end)/count(*) as youxiu,
-- sum(case when time_lapse >=6 and time_lapse <8 then 1 else 0 end)/count(*) as youxiu1,
-- sum(case when time_lapse <6 then 1 else 0 end)/count(*) as youxiu2
from ping_dialtest_back
group By NETWORK_TYPE
end
从表里查处数据,带2个参数。。如果不是存储过程,直接执行没有问题。但是做成存储过程后,执行错误。提示在
select NETWORK_TYPE,
count(*) as C,
这里就发生错误了。把查询的字段一行写一个,这样看起来很清爽舒服。但是不知道为什么创建的时候会失败。请高手帮忙看下。。
DELIMITER $$CREATE PROCEDURE SP_Ping_Dialtest (iGood INT,iStandard INT)
BEGIN
SELECT NETWORK_TYPE,
COUNT(*) AS C,
SUM(CASE WHEN CONNECTION_status='1' THEN 1 ELSE 0 END) AS D,
(SUM(CASE WHEN CONNECTION_status='1' THEN 1 ELSE 0 END) / COUNT(*)) AS chengg,
MAX(time_lapse),
MIN(time_lapse),
(SUM(time_lapse)/COUNT(*)) AS X ,
SUM(CASE WHEN time_lapse >=iGood THEN 1 ELSE 0 END)/COUNT(*) AS youxiu,
SUM(CASE WHEN time_lapse >=iStandard AND time_lapse <iGood THEN 1 ELSE 0 END)/COUNT(*) AS youxiu1,
SUM(CASE WHEN time_lapse <iStandard THEN 1 ELSE 0 END)/COUNT(*) AS youxiu2 -- sum(case when time_lapse >=8 then 1 else 0 end)/count(*) as youxiu,
-- sum(case when time_lapse >=6 and time_lapse <8 then 1 else 0 end)/count(*) as youxiu1,
-- sum(case when time_lapse <6 then 1 else 0 end)/count(*) as youxiu2
FROM ping_dialtest_back
GROUP BY NETWORK_TYPE;
END$$DELIMITER ;
正确谢谢啊。。前面也有这样测试过,加个分号然后执行的时候,MYSQL-FRONT居然死掉了,搞了好久才活过来,哈哈。。谢谢,马上结贴。