MySql 大于 group by

Question

我有下表:

account(id, balance, bank_branch)

我想选择账户余额大于其 bank_branch 平均余额的所有账户

我试过了

Select id from account where balance > (SELECT avg(balance) from account group by bank_branch)

但是子查询返回多行所以它不起作用。

我怎样才能实现这样的目标？

编辑:示例数据:

account(1, 200, A)
account(2, 400, A)
account(3, 100, B)
account(4, 300, B)

account 1,2 are in same branch - A
account 3,4 are in same branch - B

avg balance of branch A = 300
avg balance of branch B = 200

hence the output of my query should be 2,4 as
account 2 balance is 400 which is > 300(avg of A)
and account 4 balance is 300 which is > 200(avg of B)

Answer 1

相关子查询是一个选项

查询

SELECT 
 account_out.id
FROM 
 account account_out
WHERE
  EXISTS (
    SELECT 
      1
    FROM 
     account account_in   
    GROUP BY 
     account_in.bank_branch
    HAVING account_out.balance > AVG(account_in.balance) 
  );

结果

| id  |
| --- |
| 2   |
| 4   |

参见 demo

避免使用相关子查询的另一种选择是使用自连接

查询

SELECT 
 account.id
FROM (
    SELECT 
        account.bank_branch
      , AVG(account.balance) AS avg_balance
    FROM 
     account   
    GROUP BY 
     account.bank_branch
) AS account_filter
INNER JOIN 
  account
ON
    account_filter.bank_branch = account.bank_branch
  AND
    account_filter.avg_balance < account.balance

结果

| id  |
| --- |
| 2   |
| 4   |

参见 demo