时间戳

首页 > 解决方案 > 如何在 mysql 工作台中修复此存储过程我不断收到错误代码 1222

mysql - 如何在 mysql 工作台中修复此存储过程我不断收到错误代码 1222

问题描述

背景：我基本上是想向有两个或更多约会的客户展示，如果他们这样做，那么他们是首选客户，但如果他们少于两个，他们就不是首选。但是，我一直遇到上述问题。

Use plastic_surgeon; 
DROP PROCEDURE IF EXISTS sp_updated_preferred_status; 

ALTER TABLE clients 
ADD column client_status varchar (60); 

DELIMITER // 
CREATE PROCEDURE sp_updated_preferred_status () 
BEGIN  
    DECLARE client_status varchar (20); 

    Select c.ID, count(a.ID) as number_of_appts
    INTO client_status
    from clients as c
    join appointments as a
    on c.ID = a.Client_ID
    group by c.ID
    having count(a.ID) > 1 ;

    IF count(a.ID) > 1 THEN 
        SELECT 'preferred' as message;
    ELSE 
        SELECT 'not preferred' as message; 
    END IF; 

END//
DELIMITER ; 

Call sp_updated_preferred_status ();

标签： mysqlsql

解决方案

SELECT clients.ID,
       CASE WHEN COUNT(appointments.Client_ID) > 1
            THEN 'preferred'
            ELSE 'not preferred'
            END AS client_status 
FROM clients
LEFT JOIN appointments ON clients.ID = appointments.Client_ID
GROUP BY clients.ID

和分别

UPDATE clients
  JOIN ( SELECT clients.ID,
                CASE WHEN COUNT(appointments.Client_ID) > 1
                     THEN 'preferred'
                     ELSE 'not preferred'
                     END AS client_status 
         FROM clients
         LEFT JOIN appointments ON clients.ID = appointments.Client_ID
         GROUP BY clients.ID ) AS data_for_update USING (ID)
SET clients.client_status = data_for_update.client_status

推荐阅读