mysql - MySQL where 子句中子查询返回多于 1 行

Question

我在 MySQL 过程中出现错误，因为我试图在“Where”参数中选择多行。当我放置“Where = param_oid”(文件表行的 oid)时，整个过程正在工作，但我想从文件中返回几行(因此而不是为表 courrier_concerne_dossier 中存在的文件中的每个 oid 调用过程)无论如何 - 我是试图找到解决方案。

有没有简单的方法可以解决这个问题？

DROP procedure if exists `courrier_envoye_pickdossiers`;

DELIMITER $$
CREATE procedure `courrier_envoye_pickdossiers`(IN param_oid binary(16)) 
BEGIN

    set param_oid = (select oid from courrier_envoye where numero_chrono_ordre = "2632" AND numero_chrono_annee = "2013" limit 1);

    SELECT
    CAST(concat(a.prefixe_numero,a.numero, " - ", a.date_ouverture) AS CHAR) as 'noet',


    a.intitule as 'intitule',

    (select nom from gta_geoptima_data.client as m where a.client_oid = m.oid) as 'no_client',
    (select numero_client_abreviation from gta_geoptima_data.client as m where a.client_oid = m.oid) as 'sigle',
    (select nom from gta_geoptima_data.client as m where a.client_oid = m.oid) as 'nom_raison',
    (select intitule from gta_geoptima_data.direction_interne as m where a.direction_oid = m.oid) as 'service',
    a.date_livraison as 'livraison'
    FROM gta_geoptima_data.dossier as a
    WHERE a.oid = (select dossier_oid from courrier_concerne_dossier where courrier_oid = param_oid);

END$$

 call courrier_envoye_pickdossiers(null);

Subquery returns more than 1 row 0.032 sec

Answer 1

将其更改为进行联接而不是大量子查询:-

SELECT
CAST(concat(a.prefixe_numero,a.numero, " - ", a.date_ouverture) AS CHAR) AS 'noet',


a.intitule AS 'intitule',

m.nom AS 'no_client',
m.numero_client_abreviation AS 'sigle',
m.nom  AS 'nom_raison',
n.intitule AS 'service',
a.date_livraison AS 'livraison'
FROM gta_geoptima_data.dossier AS a
INNER JOIN
(
    SELECT MAX(dossier_oid) AS max_dossier_oid
    FROM courrier_concerne_dossier 
    WHERE courrier_oid = param_oid
) sub1
ON a.oid = sub1.max_dossier_oid
LEFT OUTER JOIN gta_geoptima_data.client m
ON a.client_oid = m.oid
LEFT OUTER JOIN gta_geoptima_data.direction_interne n
ON a.direction_oid = n.oid

有一个子查询，我刚刚使用MAX来获取最高匹配的dossier_oid。可以使用 MIN、LIMIT 1 或其他任何方式将数字减少到 1。或者不麻烦并返回所有匹配的数字(您可以在原始 SQL 中通过更改 WHERE a.oid = ( 到 WHERE a.oid IN ( )