MySQL存储过程编码问题 [英] MySQL Stored Procedure coding question

问题描述

我正在使用MySql和Workbench



我无法在WHERE中使用 MAX（C.LastContact） agregate获得正确结果所需的条款。我尝试过使用HAVING和CASE WHEN。两者都没有奏效。如果我可以在select语句中引入一个可用于过滤的列，那么它可以工作但如果我这样做会弄乱COUNT返回一个数字小工具所需的单行/单行号。我无法让它发挥作用。我正在使用动态SQL。以下是代码的一部分：

是否有人知道如何做到这一点？

 DELIMITER $$ 
 
  DROP   PROCEDURE   IF   EXISTS  usp_cbi_TEST_LocationMonitorCounts $$ 
  CREATE  程序 usp_cbi_TEST_LocationMonitorCounts（
 
 QueryType  varchar （ 30 ） ，
 MonitorStatus  varchar （ 50 ），
 StatusTimeToFail  int  #In分钟
）
  BEGIN  
  IF  QueryType  LIKE （' ％Count％ '）
 那么 
  SET  SelectStmt = 
 
   SELECT COUNT（distinct（C.LocationID））AS'总位置'
 
来自计算机AS C 
＃*********** ************************************************** ***************** 
 IF（（StatusTimeToFail IS NULL）OR（StatusTimeToFail =''）OR（StatusTimeToFail = 0））
 THEN 
 SET @TimeToFail = 15; #DEFAULT分钟
 ELSE 
 SET @TimeToFail = StatusTimeToFail; 
 END IF; 
＃********************************************* ********************************* 
 IF（QueryType LIKE（'％Count％'））
 THEN 
 IF（MonitorStatus！='all'）
 THEN 
 IF（MonitorStatus LIKE（'％Fail％'））
 THEN 
 SET WhereClause = CONCAT（'在哪里'）; 
 SET WhereClause = CONCAT（WhereClause，
 IFNULL（TIMESTAMPDIFF（分钟，C.LastContact，NOW（））， 0  ） ，'> ='，@ TimeToFail）; 
 
 
 ELSEIF（MonitorStatus LIKE（'％OK％'））
然后
 SET WhereClause = CONCAT（'WHERE'）; 
 SET WhereClause = CONCAT（WhereClause，
 IFNULL（TIMESTAMPDIFF（分钟，C.LastContact，NOW（））， 0  ） ，'<'，@ TimeToFail）;  

解决方案

DROP PROCEDURE IF EXISTS usp_cbi_TEST_LocationMonitorCounts

CREATE PROCEDURE usp_cbi_TEST_LocationMonitorCounts（

QueryType varchar （ 30 ），
MonitorStatus varchar （ 50 ），
StatusTimeToFail int #In Minutes
）
BEGIN
< span class =code-keyword > IF QueryType LIKE （' ％Count ％'）
那么
SET SelectStmt =

SELECT COUNT（distinct（C.LocationID））AS'Total Locations'

FROM Computers AS C
＃************************************** ****************************************
IF（（StatusTimeToFail为空）OR（StatusTimeToFail =''）OR（StatusTimeToFail = 0））
THEN
SET @TimeToFail = 15; #DEFAULT分钟
ELSE
SET @TimeToFail = StatusTimeToFail;
END IF;
＃********************************************* *********************************
IF（QueryType LIKE（'％Count％'））
THEN
IF（MonitorStatus！='all'）
THEN
IF（MonitorStatus LIKE（'％Fail％'））
THEN
SET WhereClause = CONCAT（'在哪里'）;
SET WhereClause = CONCAT（WhereClause，
IFNULL（TIMESTAMPDIFF（分钟，C.LastContact，NOW（））， 0 ） ，'> ='，@ TimeToFail）;


ELSEIF（MonitorStatus LIKE（'％OK％'））
然后
SET WhereClause = CONCAT（'WHERE'）;
SET WhereClause = CONCAT（WhereClause，
IFNULL（TIMESTAMPDIFF（分钟，C.LastContact，NOW（））， 0 ） ，'<'，@ TimeToFail）;

I am using MySql with Workbench

I have not been able to use the MAX(C.LastContact) agregate in the WHERE clause which is needed to get the correct results. I have tried using HAVING and CASE WHEN. Neither has worked. If I could bring in one more column into the select statement that could be used for filtering , it would work but if I do that it messes up the COUNT returning a SINGLE ROW / SINGLE NUMBER, needed for a Digital gadget. I have not been able to get it to work. I am using Dynamic SQL. Below is a portion of the code:
DOES ANYONE KNOW HOW THIS CAN BE DONE?

DELIMITER $$

DROP PROCEDURE IF EXISTS usp_cbi_TEST_LocationMonitorCounts$$
CREATE PROCEDURE usp_cbi_TEST_LocationMonitorCounts(

	QueryType varchar(30),
	MonitorStatus varchar(50),
	StatusTimeToFail int #In Minutes
)
BEGIN
IF QueryType LIKE ('%Count%')
THEN
		SET SelectStmt = 

		"SELECT COUNT(distinct(C.LocationID))AS 'Total Locations'
				
		FROM    Computers AS C
#******************************************************************************
IF ((StatusTimeToFail IS NULL) OR (StatusTimeToFail ='') OR (StatusTimeToFail =0))
THEN
	SET @TimeToFail = 15; #DEFAULT minutes
ELSE	
	SET @TimeToFail = StatusTimeToFail;
END IF;
#******************************************************************************
IF (QueryType LIKE ('%Count%'))
THEN
	IF (MonitorStatus != 'all')
	THEN
		IF (MonitorStatus LIKE('%Fail%'))
		THEN
				SET WhereClause = CONCAT(' WHERE ');
				SET WhereClause = CONCAT(WhereClause,
 " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",   '>=', @TimeToFail);


		ELSEIF (MonitorStatus LIKE('%OK%'))
		then
				SET WhereClause = CONCAT(' WHERE ');				
				SET WhereClause = CONCAT(WhereClause,
 " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",  '<', @TimeToFail);

解决方案

DROP PROCEDURE IF EXISTS usp_cbi_TEST_LocationMonitorCounts

CREATE PROCEDURE usp_cbi_TEST_LocationMonitorCounts( QueryType varchar(30), MonitorStatus varchar(50), StatusTimeToFail int #In Minutes ) BEGIN IF QueryType LIKE ('%Count%') THEN SET SelectStmt = "SELECT COUNT(distinct(C.LocationID))AS 'Total Locations' FROM Computers AS C #****************************************************************************** IF ((StatusTimeToFail IS NULL) OR (StatusTimeToFail ='') OR (StatusTimeToFail =0)) THEN SET @TimeToFail = 15; #DEFAULT minutes ELSE SET @TimeToFail = StatusTimeToFail; END IF; #****************************************************************************** IF (QueryType LIKE ('%Count%')) THEN IF (MonitorStatus != 'all') THEN IF (MonitorStatus LIKE('%Fail%')) THEN SET WhereClause = CONCAT(' WHERE '); SET WhereClause = CONCAT(WhereClause, " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ", '>=', @TimeToFail); ELSEIF (MonitorStatus LIKE('%OK%')) then SET WhereClause = CONCAT(' WHERE '); SET WhereClause = CONCAT(WhereClause, " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ", '<', @TimeToFail);

这篇关于MySQL存储过程编码问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！