MySQL问题：是否可以在存储过程中的SELECT语句中设置IF语句

问题描述：

我正在使用MySql和Workbench

我无法在WHERE中使用 MAX（C.LastContact） agregate获得正确结果所需的条款。我想另一种方法可能是根据传递给存储过程的参数值在SELECT语句中设置IF语句。这可能吗。我无法让它发挥作用。我正在使用动态SQL。以下是部分代码：

SET SelectStmt =

SELECT COUNT（distinct（C.LocationID））AS''总位置''，

如果Parameter1 =''all ''

那么这样做

IF（（选择最大值（TIMESTAMPDIFF（分钟，C1.LastContact），现在（）））
来自计算机c1的
，其中c1.locationid = c.locationid）> = @TimeToFail，''失败''，''确定''）

AS''状态''，

如果参数1 =''失败''

那么这样做

如果参数1 =''确定''

那么这样做

来自

计算机AS C;

SET WhereClause = CONCAT（WhereClause，IFNULL（TIMESTAMPDIFF（分钟，C.LastContact，NOW（）），0），

''> =''，@ TimeToFail）;

SET GroupBy =GROUP BY C.LocationID;

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 am thinking another way to do this might be to set up IF statements in the SELECT statement based on the value of a parameter passed into the Stored Procedure. Is this possible. I have not been able to get it to work. I am using Dynamic SQL. Below is a portion of the code:

SET SelectStmt =

"SELECT COUNT(distinct(C.LocationID))AS ''Total Locations'',

IF Parameter1 = ''all''
THEN do this

IF((select max(TIMESTAMPDIFF(minute, C1.LastContact, NOW() ))
from computers c1 where c1.locationid = c.locationid) >= @TimeToFail,''Fail'',''OK'')
AS ''Status'',

IF Parameter1 = ''fail''
THEN do this

IF Parameter1 = ''ok''
THEN do this

FROM
Computers AS C ";

SET WhereClause = CONCAT(WhereClause, " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",
''>='',@TimeToFail);

SET GroupBy = " GROUP BY C.LocationID ";

答

您可以使用CASE WHEN THEN语句来实现此功能。一个例子如下。

you can use CASE WHEN THEN statement to achieve this functionality. an example for this is as follows.

SET SelectStmt =


"SELECT COUNT(distinct(C.LocationID))AS 'Total Locations',

CASE

WHEN Parameter1 = 'all'
THEN --do this


--IF((select max(TIMESTAMPDIFF(minute, C1.LastContact, NOW() ))
--from computers c1 where c1.locationid = c.locationid) >= @TimeToFail,'Fail','OK')
--AS 'Status',


WHEN Parameter1 = 'fail'
THEN --do this


WHEN Parameter1 = 'ok'
THEN --do this

END AS 'Status'

FROM
Computers AS C ";

SET WhereClause = CONCAT(WhereClause, " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",
'>=',@TimeToFail);

SET GroupBy = " GROUP BY C.LocationID ";

请检查并回复是否有效。

please check and reply if it works.

MySQL问题：是否可以在存储过程中的SELECT语句中设置IF语句

相关推荐