mysql - 子查询返回 "single-row subquery returning more than one row"的 SQL 查询？

Question

这是我的查询:

SELECT TRANS_MSTR.branch_id
  FROM TRANS_MSTR, BRANCH_MSTR
 WHERE BRANCH_MSTR.branch_type_desc = 'ATM ONLY' 
   And ( SELECT SUM(TRANS_MSTR.trans_amount)  
           FROM TRANS_MSTR 
          WHERE TRANS_MSTR.trans_yyyymm = '201511' 
       GROUP BY TRANS_MSTR.branch_id) < 100;

"ORA-01427: single-row subquery returns more than one row"

是我得到的错误。我真的不知道那是什么意思？我的查询总体目标是返回分支 ID 和总和，其中总和小于 100。

当我尝试这样修复它时:

     SELECT TRANS_MSTR.branch_id, 
           (SELECT SUM(TRANS_MSTR.trans_amount) 
              FROM TRANS_MSTR 
             WHERE TRANS_MSTR.trans_yyyymm = '201511' 
          GROUP BY TRANS_MSTR.branch_id) as TransAmt
              FROM TRANS_MSTR, BRANCH_MSTR
             WHERE BRANCH_MSTR.branch_type_desc = 'ATM ONLY' 
               And TransAmt < 100;

它告诉我 TRANSAMT 是一个无效的标识符。但我认为您可以为子查询设置别名？

Answer 1

请试试这个:

    SELECT BRANCH_MSTR.branch_id,  SUM(TRANS_MSTR.trans_amount) 
        AS TransAmt FROM BRANCH_MSTR b
INNER JOIN BRANCH_MSTR 
        ON TRANS_MSTR.branch_id = BRANCH_MSTR.branch_id 
     WHERE BRANCH_MSTR.branch_type_desc = 'ATM ONLY' 
       AND TRANS_MSTR.trans_amount < 100 
       AND TRANS_MSTR.trans_yyyymm = '201511' 
  GROUP BY TRANS_MSTR.branch_id;