我有 3 個表和以下條目：
*users [id, name]*
insert into users (1, 'jack')
*portfolios [id, user_id, status]*
insert into portfolios (1, 1, 'active')
insert into portfolios (2, 1, 'active')
*deposits [id, portfolio_id, amount]*
insert into deposits (1, 1, 10)
insert into deposits (2, 2, 5)
*users [id, name]*
insert into users (2, 'jill')
*portfolios [id, user_id, status]*
insert into portfolios (3, 2, 'active')
insert into portfolios (4, 2, 'pending')
insert into deposits (3, 3, 3)
insert into deposits (4, 4, 4)
我需要創建一個查詢來顯示每個用戶的總金額，其中連接的 portoflio 是“活動的。基本上，根據上面的插入，我應該有：
name: 'jack', total_amount: 15
name: 'jill', total_amount: 3
我需要使用聚合嗎？
uj5u.com熱心網友回復：
查詢應該是
SELECT u.name, SUM(d.amount) as total_amount FROM users u, deposits d, portfolios p
WHERE u.id=p.user_id and p.id=d.portfolio_id and p.status='active'
GROUP BY u.id
uj5u.com熱心網友回復：
您應該加入，最好使用顯式加入，過濾active portfolio.status聚合users.name和求和deposits.amount
select
users.name, sum(deposits.amount) as total_amount /*each distinct user and the sum for their amount*/
from users
inner join portfolio on users.id = portfolio.user_id /*use explicit joins on corresponding ids*/
inner join deposits on portfolio.id = deposits.portfolio_id
where portfolio.status = 'active' /*filter for active only*/
group by users.name /*this gives unique list of users*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484572.html