mysql - MySQL中如何组合GROUP BY和JOIN语句？

Question

我正在尝试将在一个表上运行的查询与另一个表上的某些列结合起来。我运行的查询通过以下方式计算我的总和:

SELECT security.Loan_id
     , SUM(security.SecMktValue) AS TotalSecMktValue
  FROM security 
 GROUP 
    BY Loan_id
 ORDER 
    BY loan_id ASC;

然后我尝试通过以下方式将此查询与另一个表中的列连接起来:

SELECT loans.Loan_id, loans.TotalLoanAmt
FROM loans
JOIN(SELECT SUM(security.SecMktValue) AS TotalSecMktValue,security.Loan_id
FROM security
GROUP BY Loan_id ASC)
ON loans.Loan_id = security.Loan_id;

但是，这不会运行，因为它说我的 SQL 语法有错误，即使没有任何红色下划线。有谁知道这是为什么吗？

Answer 1

您缺少别名:

SELECT loans.Loan_id, loans.TotalLoanAmt
FROM loans
JOIN(
    SELECT SUM(security.SecMktValue) AS TotalSecMktValue,security.Loan_id
    FROM security
    GROUP BY Loan_id ASC
    ) security -- <-- here
ON loans.Loan_id = security.Loan_id;