MySQL存储过程迭代多个值而没有使用游标返回错误代码：1172。结果由多行组成

I created this MySQL Stored Procedure as a solution of my problem mentioned in this post on SO.

我创建了这个MySQL存储过程作为我在SO上提到的问题的解决方案。

Here is the procedure:

这是程序：

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE n, i INT DEFAULT 0;
DECLARE pid VARCHAR(20);
DROP temporary table if exists tmp;
CREATE temporary table tmp
SELECT 
    t1.patient_id
FROM 
    consultation t1
LEFT JOIN diagnosis t2 
    ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 
    ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 
    ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 
    ON t5.patient_id = t4.patient_id
WHERE 
    t2.diagnosis_name LIKE '%Diabetes%' 
    AND t1.clinic_id = '361'
    AND t3.visit_status="Active"
GROUP BY t1.patient_id ;

set i=1;
SELECT count(*) INTO n FROM tmp;
SELECT patient_id into pid FROM tmp;
while i<=n DO
    set pid = (select patient_id from tmp);
SELECT
    t1.patient_id,
    CONVERT(aes_decrypt(t4.patient_name_en, 'key') USING utf8mb4) as patient_name_en,

    min(t3.date_of_visit) as date_of_visit, 
    t2.diagnosis_name,
    max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
    ifnull(t5.assessment_result, 0) as assessment_result 
FROM consultation t1
LEFT JOIN diagnosis t2
    ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3
    ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4
    ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5
    ON t5.patient_id = t4.patient_id
WHERE
   t2.diagnosis_name LIKE '%Diabetes%' AND
   t1.patient_id = pid AND
   t1.clinic_id = '361' AND
   t3.visit_status="Active"
GROUP BY
    t1.patient_id,
    t2.diagnosis_name,
    t3.date_of_visit,
    t4.patient_name_en,
    t5.date_of_assessment,
    t5.assessment_result
ORDER BY t5.date_of_assessment DESC LIMIT 1; 
set i = i + 1;
END WHILE;
END

I will explain it a bit. The following query will get the patient_ids group by patient_id, so the problem of only full group by is resolved:

我会解释一下。以下查询将通过patient_id获取patient_ids组，因此仅解决了完整分组的问题：

DROP temporary table if exists tmp;
CREATE temporary table tmp
SELECT t1.patient_id
FROM consultation t1
LEFT JOIN diagnosis t2 
    ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 
    ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 
    ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 
    ON t5.patient_id = t4.patient_id
WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
AND t3.visit_status="Active"
GROUP BY t1.patient_id ;

Then I will count how many rows I had in this temporary table:

然后我会计算这个临时表中有多少行：

set i=1;
SELECT count(*) INTO n FROM tmp;

The result is 2 patient IDs.

结果是2个患者ID。

So I need to iterate through each patient id:

所以我需要遍历每个患者ID：

select patient_id into pid FROM tmp;

inside this query:

在这个查询里面：

while i<=n DO
set pid = (select patient_id from tmp);
SELECT
    t1.patient_id,
    CONVERT(aes_decrypt(t4.patient_name_en, 'key') USING utf8mb4) as patient_name_en,
    min(t3.date_of_visit) as date_of_visit, 
    t2.diagnosis_name,
    max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
    ifnull(t5.assessment_result, 0) as assessment_result 
FROM consultation t1
LEFT JOIN diagnosis t2
    ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3
    ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4
    ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5
   ON t5.patient_id = t4.patient_id
WHERE
   t2.diagnosis_name LIKE '%Diabetes%' AND
   t1.patient_id = pid AND
   t1.clinic_id = '361' AND
   t3.visit_status="Active"
GROUP BY
    t1.patient_id,
    t2.diagnosis_name,
    t3.date_of_visit,
    t4.patient_name_en,
    t5.date_of_assessment,
    t5.assessment_result
ORDER BY t5.date_of_assessment DESC LIMIT 1; 
set i = i + 1;
END WHILE;

I think my problem is in these 2 lines:

我认为我的问题在于以下两行：

select patient_id into pid FROM tmp;

And

和

set pid = (select patient_id from tmp);

The error is:

错误是：

I don't want to use cursors, as our professor once said that cursors are resourceful and are bad practice.

我不想使用游标，因为我们的教授曾经说游标是足智多谋而且是不好的做法。

Error Code: 1172. Result consisted of more than one row

错误代码：1172。结果包含多行

1 个解决方案

#1

If you are getting two different patiend ids and want to iterate through both then you need to use CURSOR, e.g.:

如果你得到两个不同的patiend id并且想要迭代两者，那么你需要使用CURSOR，例如：

DECLARE patient_id_cursor CURSOR FOR SELECT patient_id FROM tmp;

OPEN patient_id_cursor;
FETCH cursor_name INTO pid;
/*
Logic to process `pid`
*/
CLOSE patient_id_cursor;