MySQL修复错误：子查询返回1个以上的行

goucqfw6 于 12个月前发布在 Mysql

关注(0)|答案(1)|浏览(67)

我理解这个错误，我知道是什么触发了它，问题是我无法解决它。我得到一个空的子查询或此错误。我确信解决办法是显而易见的，但我的思想正把我引向错误的方向。。
子查询为：

SELECT DISTINCT 
    IFNULL(GET_BEZNR(T050.BEZNR, @SPRACHNR), '') AS `NAME`, 
    IF(T050.TYP <> 'K', 
        T210.KRITWERT, 
        IFNULL(GET_BEZNR_FOR_KEY_TABLE(T050.TABNR, T210.KRITWERT, @SPRACHNR), '') 
    ) AS `VALUE` 
FROM `200_fixed` AS `P` 
    JOIN `200` AS T200 ON T200.ARTNR = P.ARTNR AND T200.DLNR = P.DLNR 
    JOIN `210` AS T210 ON T210.ARTNR = T200.ARTNR AND T210.DLNR = T200.DLNR 
    JOIN `050` AS T050 ON T050.DLNR IN (T200.DLNR, 9999) AND T050.KRITNR = T210.KRITNR 
WHERE P.ARTNR_SHORT = CLEAN_NUMBER(@ARTNR) 
    AND P.DLNR = @DLNR 
ORDER BY `NAME`;

而且它工作得很完美。它可以不返回结果，也可以返回很多结果，我需要将这些结果连接在一个字符串中，以便能够将其用作子查询。
问题是当我将它“集成”到另一个查询中时，就像这样：

SELECT DISTINCT
...

    -- CRYTERIES
    IFNULL(CONCAT_WS('|',(
        SELECT 
            CONCAT_WS(' ', 
                IFNULL(GET_BEZNR(C050.BEZNR, @SPRACHNR), ''), 
                IF(C050.TYP <> 'K', 
                    C210.KRITWERT, 
                    IFNULL(GET_BEZNR_FOR_KEY_TABLE(C050.TABNR, C210.KRITWERT, @SPRACHNR), '') 
                ) 
            ) 
        FROM `200_fixed` AS `C` 
            JOIN `200` AS C200 ON C200.ARTNR = C.ARTNR AND C200.DLNR = C.DLNR 
            JOIN `210` AS C210 ON C210.ARTNR = C200.ARTNR AND C210.DLNR = C200.DLNR 
            JOIN `050` AS C050 ON C050.DLNR IN (C200.DLNR, 9999) AND C050.KRITNR = C210.KRITNR 
        WHERE C.ARTNR_SHORT = CLEAN_NUMBER(T200.ARTNR) 
            AND C.DLNR = T200.DLNR)), '') AS `CRYTERIES`, 

...

FROM `301` AS `T301` 
    INNER JOIN `302` AS `T302` ON T302.NODE_ID = T301.NODE_ID 
    INNER JOIN `400` AS `T400` ON T302.GENARTNR = T400.GENARTNR 
    INNER JOIN `320` AS `T320` ON T320.GENARTNR = T400.GENARTNR 
    INNER JOIN `200` AS `T200` ON T200.ARTNR = T400.ARTNR AND T200.DLNR = T400.DLNR 
    ...
WHERE ...;

主查询在没有这个子查询的情况下工作得很好，没有必要在上面浪费脑细胞。T200.ARTNR和T200.DLNR来自主查询。
我尝试做的是通过空格（CONCAT_WS(' ', ...）连接NAME和VALUE，然后通过|（CONCAT_WS('|',）连接子查询的所有结果，如果子查询没有返回结果，则简单地返回''（IFNULL(..., '')）

mysql

来源：https://stackoverflow.com/questions/76508491/mysql-fix-error-subquery-returns-more-than-1-row

1条答案

按热度按时间

wswtfjt71#

由于消息明确指出您有多个conatinated名称，因此将返回该名称，但只允许一个标量值
这样奥尤就可以

IFNULL(CONCAT_WS('|',
                (SELECT 
                        CONCAT_WS(' ',
                                    IFNULL(GET_BEZNR(C050.BEZNR, @SPRACHNR), ''),
                                    IF(C050.TYP <> 'K',
                                        C210.KRITWERT,
                                        IFNULL(GET_BEZNR_FOR_KEY_TABLE(C050.TABNR, C210.KRITWERT, @SPRACHNR),
                                                '')))
                    FROM
                        `200_fixed` AS `C`
                            JOIN
                        `200` AS C200 ON C200.ARTNR = C.ARTNR
                            AND C200.DLNR = C.DLNR
                            JOIN
                        `210` AS C210 ON C210.ARTNR = C200.ARTNR
                            AND C210.DLNR = C200.DLNR
                            JOIN
                        `050` AS C050 ON C050.DLNR IN (C200.DLNR , 9999)
                            AND C050.KRITNR = C210.KRITNR
                    WHERE
                        C.ARTNR_SHORT = CLEAN_NUMBER(T200.ARTNR)
                            AND C.DLNR = T200.DLNR ORDER BY 1 LIMIT 1)),
        '')

并将其缩减为一个条目。
也可能是GROUP BY或ROW_NUMBER，它们只为该选择提供一个值
如果需要所有名称，则必须使用GROUP_CONCAT，如

GROUP_NCONCAT(IFNULL(CONCAT_WS('|',
                (SELECT 
                        CONCAT_WS(' ',
                                    IFNULL(GET_BEZNR(C050.BEZNR, @SPRACHNR), ''),
                                    IF(C050.TYP <> 'K',
                                        C210.KRITWERT,
                                        IFNULL(GET_BEZNR_FOR_KEY_TABLE(C050.TABNR, C210.KRITWERT, @SPRACHNR),
                                                '')))
                    FROM
                        `200_fixed` AS `C`
                            JOIN
                        `200` AS C200 ON C200.ARTNR = C.ARTNR
                            AND C200.DLNR = C.DLNR
                            JOIN
                        `210` AS C210 ON C210.ARTNR = C200.ARTNR
                            AND C210.DLNR = C200.DLNR
                            JOIN
                        `050` AS C050 ON C050.DLNR IN (C200.DLNR , 9999)
                            AND C050.KRITNR = C210.KRITNR
                    WHERE
                        C.ARTNR_SHORT = CLEAN_NUMBER(T200.ARTNR)
                            AND C.DLNR = T200.DLNR )),
        ''))

赞(0）回复(0）举报 12个月前

我来回答

MySQL修复错误：子查询返回1个以上的行

1条答案

相关问题

热门标签

最新问答