mysql - MySQL，如何在SELECT查询中引用子查询字段

我试图使用以下查询计算MySQL中的帐户余额

SELECT accountNumber,
(
    SELECT  amount
    FROM    accountDebits
    WHERE   accountNumber = accounts.accountNumber
) debits,
(
    SELECT  amount
    FROM    accountCredits
    WHERE   accountNumber = accounts.accountNumber
) credits,
credits - debits as balance
FROM    accounts

但我有个错误：
错误代码：1054。“字段列表”中的未知列“credits”
如何引用select中的子查询别名？

最佳答案

用另一个执行计算的子查询包装它：

SELECT accountNumber, debits, credits, credits - debits as balance
FROM (
  SELECT accountNumber,
  (
    SELECT  amount
    FROM    accountDebits
    WHERE   accountNumber = accounts.accountNumber
  ) debits,
  (
    SELECT  amount
    FROM    accountCredits
    WHERE   accountNumber = accounts.accountNumber
  ) credits
  FROM accounts
  ) x

当这使您的查询启动并运行时，最好使用外部连接：

SELECT accountNumber, debits, credits, credits - debits as balance
FROM (
  SELECT
      a.accountNumber,
      coalesce(sum(d.amount), 0) debits,
      coalesce(sum(c.amount), 0) credits,
  FROM accounts a
  LEFT JOIN accountDebits d
    ON d.accountNumber = a.accountNumber
  LEFT JOIN accountCredits c
    ON c.accountNumber = a.accountNumber
) x

关于mysql - MySQL，如何在SELECT查询中引用子查询字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38987602/