SQL查询，获取一列的MIN，并将其与其他列的值相乘

Question

我尝试进行一个sql查询，通过将当前金额乘以所有计数的最低利率，返回客户的ID，其帐户的货币及其帐户的年终金额

桌子就像

**customer**       **account**
 id                  id
 name                customer_id
 surname             curreny
                     cur_Amount
                     interest_rate

我做了一个如下所示的查询，但它只返回具有最低利率的一行。

SELECT customer.id, account.currency, account.cur_amount, MIN(account.interest_rate)*cur_amount AS End_Year_Balance FROM customer, account LEFT JOIN account on customer.id = account.customer_id

如何通过增加最低利率来列出具有客户 ID 和年末余额的所有账户？

谢谢

Answer 1

这将

适用于MySQL

SELECT min(interest_rate) into @min_interest from account;
SELECT customer.id,account.currency,account.cur_amount,@min_interest*cur_amount AS End_Year_Balance 
FROM customer 
LEFT JOIN account 
ON customer.id = account.customer_id;

这将适用于任何数据库：

SELECT customer.id,account.currency,account.cur_amount,temp.min_interest*cur_amount AS End_Year_Balance 
FROM customer 
LEFT JOIN account ON customer.id = account.customer_id
LEFT JOIN (SELECT min(interest_rate) as min_interest from account) temp ON 1=1;

Answer 2

SELECT
    customer.id, customer.name, customer.surname,
    account.id, account.currency, account.cur_Amount, account.interest_rate,
    account.cur_Amount * A2.LowestInterestRate
FROM
    customer
    INNER JOIN account ON (customer.id = account.customer_id)
    LEFT JOIN 
        (SELECT customer.id, MIN(interest_rate) LowestInterestRate 
         FROM account 
         GROUP BY customer.id
    ) AS A2 ON (customer.id = A2.customer_id)

Answer 3

您需要从查询中删除多义性，如果使用 AGGREGATE 函数，则应使用 GROUP BY。但是，这是一个适用于类似情况的示例查询。

select a.custid, a.custname, a.surname, 
b.accid, b.currency, b.curamount, b.interestrate, b.EndYearBalance 
from customer a 
left outer join 
(
select accid, custid, currency, curamount, MIN(interestrate) AS interestrate, 
ISNULL(MIN(interestrate)*curamount, 0) AS EndYearBalance 
from  account 
group by accid, custid, currency, curamount   
) as b on a.custid = b.custid 

Answer 4

这应该有效并且非常简单：

SELECT
    c.id,
    a.currency,
    a.cur_amount,
    a.cur_amount * (SELECT MIN(interest_rate) FROM account)
FROM
    customer c left join account a on c.id = a.customer_id