SQL IF SELECT查询为null，然后执行另一个查询 [英] SQL IF SELECT query is null then do another query

问题描述

我有一个定期返回"nothing"的查询，如果是这种情况，我想运行一个不同的查询，但是我不知道这样做的方式.如果有人可以帮忙.

I have a query that regularly returns "nothing", and I would like to run a different query if this is the case, but I know not of the way of doing this. If anyone could be of help please.

这是我正在使用的当前代码...

Here is the current code I am using...

SELECT * FROM cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId = '0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName

我基本上想说

IF <my code> IS NULL THEN <do other code>, IF <my code> IS NOT NULL THEN return the result.

谢谢

推荐答案

一种实现方法就是这样

• 设置两个等于要执行的查询的变量.

• set two variables equal to the queries you want to execute.

当第一个不为null时，将另一个变量设置为等于正确查询的变量.

set another variable equal to the correct query when the first is not null.

使用存储过程执行该查询.

execute that query with a stored procedure.

存储过程:

DELIMITER $$

CREATE PROCEDURE `dynamic_query`(in input varchar(255))
BEGIN
    SET @a := input;
    PREPARE stmt FROM @a;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;

您要执行的两个选择:

SET @A := "SELECT * FROM  cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName";
SET @B := "your other select here";

获得正确查询的定义:

SET @C := (
SELECT
    CASE 
        WHEN EXISTS
            (   SELECT * 
                FROM  cfg_users 
                JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid 
                WHERE iTeamId='0' 
                    AND sDisabled IS NULL 
                    AND iStatusId > 0 
                    AND sDate = '2014-08-01' 
                GROUP BY cfg_users.iUserId 
                ORDER BY iStatusId, sName
            )
        THEN @A
        ELSE @B
    END
);

执行语句:

CALL dynamic_query(@C);

• 在查询存在时进行演示
• 当查询不存在时进行演示
• DEMO WHEN THE QUERY EXISTS
• DEMO WHEN THE QUERY DOESN'T EXIST

这篇关于SQL IF SELECT查询为null，然后执行另一个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！