mysql if语句只能在存储过程中使用,MySQL问题：是否可以在存储过程中的SELECT语句中设置IF语句...-优快云博客

在MySQL Workbench中，遇到无法在WHERE子句中使用MAX(C.LastContact)聚合的问题。提出了通过在SELECT语句中设置IF语句作为替代方案，根据传递到存储过程的参数值来实现相同功能。示例代码展示了如何使用CASE WHEN语句来达成目的，同时调整了WHERE子句和GROUP BY子句。这种方法允许在不同参数条件下动态改变查询条件。

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 ";

解决方案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.