使用变量的子查询返回多行错误，需适配多记录SQL查询

问题说明 #

需要通过格式为<fleetnumber>T（如393T）的卡车车队编号，对应找到格式为<fleetnumber>L（如393L）的拖车注册号。当前SQL查询在单条记录时正常，但移除WHERE子句返回多条记录时，会抛出Subquery returns more than 1 row错误。现有代码如下：

SELECT
    h.TRANSACTION_NUMBER,
    a.RECORD_ID AS `A_ASSET_ID`,
    @FleetNumber := a.FLEET_NUMBER,
    (
        SELECT a1.REGISTRATION_NUMBER
        FROM newtodme_newton_web_portal.ASSETS a1
        WHERE a1.FLEET_NUMBER = REPLACE(@FleetNumber, 'T', 'L')
    ) AS `TRAILER_REG`
FROM newtodme_newton_hand_over.HANDOVERS h
LEFT JOIN newtodme_newton_web_portal.ASSETS a ON h.ASSET_ID = a.RECORD_ID;
/*WHERE h.TRANSACTION_NUMBER = 4000*/

解决方法 #

1. 改用JOIN关联（最优方案） #

将子查询替换为LEFT JOIN，避免单行子查询返回多行的问题，同时逻辑更直观，还能省去多余的用户变量：

SELECT
    h.TRANSACTION_NUMBER,
    a.RECORD_ID AS `A_ASSET_ID`,
    a.FLEET_NUMBER,
    a1.REGISTRATION_NUMBER AS `TRAILER_REG`
FROM newtodme_newton_hand_over.HANDOVERS h
LEFT JOIN newtodme_newton_web_portal.ASSETS a 
    ON h.ASSET_ID = a.RECORD_ID
LEFT JOIN newtodme_newton_web_portal.ASSETS a1 
    ON a1.FLEET_NUMBER = REPLACE(a.FLEET_NUMBER, 'T', 'L');

该写法能保证所有HANDOVER记录都被返回，即使没有对应拖车，TRAILER_REG会显示为NULL。

2. 限制子查询返回单行 #

如果必须保留子查询结构，可添加LIMIT 1确保子查询仅返回一条记录（注意：需确认业务上每个<fleetnumber>L唯一，否则会丢失数据）：

SELECT
    h.TRANSACTION_NUMBER,
    a.RECORD_ID AS `A_ASSET_ID`,
    a.FLEET_NUMBER,
    (
        SELECT a1.REGISTRATION_NUMBER
        FROM newtodme_newton_web_portal.ASSETS a1
        WHERE a1.FLEET_NUMBER = REPLACE(a.FLEET_NUMBER, 'T', 'L')
        LIMIT 1
    ) AS `TRAILER_REG`
FROM newtodme_newton_hand_over.HANDOVERS h
LEFT JOIN newtodme_newton_web_portal.ASSETS a 
    ON h.ASSET_ID = a.RECORD_ID;

同时移除了@FleetNumber变量，直接使用a.FLEET_NUMBER避免变量在多行记录中可能的异常传递问题。

3. 用聚合函数强制单行返回 #

若业务允许，可使用MAX()或MIN()聚合函数让子查询返回单行结果：

SELECT
    h.TRANSACTION_NUMBER,
    a.RECORD_ID AS `A_ASSET_ID`,
    a.FLEET_NUMBER,
    (
        SELECT MAX(a1.REGISTRATION_NUMBER)
        FROM newtodme_newton_web_portal.ASSETS a1
        WHERE a1.FLEET_NUMBER = REPLACE(a.FLEET_NUMBER, 'T', 'L')
    ) AS `TRAILER_REG`
FROM newtodme_newton_hand_over.HANDOVERS h
LEFT JOIN newtodme_newton_web_portal.ASSETS a 
    ON h.ASSET_ID = a.RECORD_ID;

内容的提问来源于stack exchange，提问作者Madeline Kallis