case 查询语句中使用的 MySQL 计数

Question

我要求此查询根据 COUNT 返回的值执行 CASE 语句:

select   @getshift :=  (select(COUNT(*) from `oee_machinenames` where `Shift1` = '06:00:00'))
(Case
When  @getshift >=1 
Then
update `oee_system`.`oee_machinenames` 
set `oee_machinenames`.`default_GM_state` = 0 
where `oee_machinenames`.`Shift1` = '06:00:00'
when @getshift ==0 
then
update `oee_system`.`oee_machinenames` 
set `oee_machinenames`.`default_GM_state` = 9 
where `oee_machinenames`.`Shift1` = '06:00:00' end)

变量@getshift应该返回计数的数量，如果>= 1则发生更新语句，如果不是则应发生另一个更新语句。我最终将在 MYSQL 时间触发事件中使用它

编辑:这都不能作为存储过程:

CREATE DEFINER=`tpm_dev`@`%` PROCEDURE `getshifts`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
select  @getshift := select COUNT(*) from oee_machinenames where oee_machinenames.Shift1 = '06:00:00';
update oee_machinenames
if @getshift >=1 
set default_GM_state = 99;
else
set default_GM_state = 90;
end if
END 

Answer 1

您可以对子查询进行联接。然而，您正在更新子查询使用的同一个表，而 MySQL 不支持这一点。您也许可以通过添加一个额外的子查询来从子查询中选择结果，从而对 MySQL 隐藏公共(public)表。

类似这样的:-

UPDATE oee_system.oee_machinenames a
INNER JOIN 
(
    SELECT aCount
    FROM
    (
        SELECT COUNT(*) AS aCount
        FROM oee_machinenames 
        WHERE Shift1 = '06:00:00'
    ) c
) b
set a.default_GM_state = IF(b.aCount = 0, 0, 99) 
WHERE b.aCount >= 0
AND a.Shift1 = '06:00:00'