SQL 3 与分组依据和求和函数联接 [重复]

Question

我有3张表＜br＞Tbcodetable＜br＞

    codevalue | codename | desc1 | desc2 
    1         | SATO     | NAG   | Naga
    2         | SATO     | BAG   | Baguio
    3         | SATO     | NCR   | Head Office

Tbmember

employeeno | capcon_accountno | savings_accountno | sato
1          | 00101            | 00201             | NCR
2          | 00102            | 00202             | BAG

TB存款< br >

employeeno | account_no | accountbalance
1          | 00101      | 1000
1          | 00201      | 5000
2          | 00102      | 1000
2          | 00202      | 5000

我想在一个查询中查询每个sato的资本和储蓄的总和

codename | codevalue | desc1      | desc2 | capcon | savings
SATO     |3          |Head Office |NCR    |1000    | 5000
SATO     |2          |Baguio      |BAG    |1000    | 5000
SATO     |1          |Naga        |NAG    |0       | 0

我能做的最好的查询是

SELECT codename,codevalue,desc1,desc2,sum(b.accountbalance) as capcon 
  FROM TBCODETABLE c 
  left join TBMEMBER a on c.desc2 = a.SatoCode 
  html" target="_blank">join tbdeposit b on a.employeeno = b.employeeno 
 where a.SLAStatus = 'A' and c.codename ='sato'
group by codename, codevalue, desc1, desc2

这给了我这个结果。

codename | codevalue | desc1      | desc2 | capcon
SATO     |1          |Head Office |NCR    |1000
SATO     |2          |Baguio      |BAG    |1000

这只给了我资本的总和（我迷失了如何包括储蓄和资本的结果），它不包括那牙地区
。

我可以通过employeeno连接tbmember和tbdeposit，但是我不知道如何在结果集中分离资本和储蓄的总和

编辑< br >我编辑了查询，现在多亏了syed，我可以查询两者，但我仍然无法获得NAG sato代码。查询:< br >

SELECT codename,codevalue,desc1,desc2, coalesce(sum(b.accountbalance),0) as savings, coalesce(sum(bb.accountbalance),0) as capcon FROM TBCODETABLE c left join TBMEMBER a on c.desc2 = a.SatoCode join tbdeposit b on a.SAVINGS_AccountNo = b.AccountNo 
inner join tbdeposit bb on a.CAPCON_Accountno = bb.AccountNo where a.SLAStatus = 'A'
group by codename, codevalue, desc1, desc2

我能够得到预期的结果，谢谢你们克里斯的左加入建议解决了我的NAG SATO问题，尽管赛义德的回复帮助我花了很多时间。
我不知道如何投赞成票，所以我只会投克里斯（第一次在这里）

我的最后一个问题是：

SELECT codename,codevalue,desc1,desc2, coalesce(sum(b.accountbalance),0) as savings, coalesce(sum(bb.accountbalance),0) as capcon FROM TBCODETABLE c left join TBMEMBER a on c.desc2 = a.SatoCode left join tbdeposit b on a.SAVINGS_AccountNo = b.AccountNo 
left join tbdeposit bb on a.CAPCON_Accountno = bb.AccountNo where a.SLAStatus = 'A' or codename = 'sato'
group by codename, codevalue, desc1, desc2

Answer 1

试试这个

select codename,codevalue,desc1,desc2,coalesce(sum(accountbalance),0) as capcon,
coalesce(deposite,0) as saving 
from tbcodetable tc  
left join tbmember tb 
on tc.codevalue = tb.employeeno  
left join(select max(employeeno) as employee,max(accountbalance) as deposite,accountbalance,employeeno from tbdeposite tp group by employeeno)l 
on tb.employeeno = l.employeeno group by codevalue;

演示

Answer 2

我认为您需要在子查询中按< code>employeeno和< code>account_no进行分组。然后将capcon和savings拆分到单独的列中，并使用< code>case when:

select codename,
       codevalue,
       desc1,
       desc2,
       sum( case when a.savings_accountno = b.account_no then accountbalance else 0 end ) as savings,
       sum( case when a.capcon_accountno = b.account_no then accountbalance else 0 end ) as capcon
from   tbcodetable as c
       left join
       tbmember as a
       on c.desc1 = a.sato
       left join
       (
         select employeeno,
                account_no,
                sum( accountbalance ) as accountbalance
         from   tbdeposit
         group by employeeno,
                account_no
       ) as b
       on a.employeeno = b.employeeno
 group by codename,
       codevalue,
       desc1,
       desc2

在这里查看SQLfiddle

Answer 3

首先，当你在表格中加入一些东西时，你左边的JOININGed你需要让它也成为一个左连接，否则你最终会得到一个内部连接等价物。这不是你唯一的问题。

其次，对于值比较，大小写很重要，所以如果你的表样本是正确的，你应该有c.codename='SATO'。

第三，您可能在那个时候，想要在 SUM 中添加一个 case 语句，以便：

SUM(case 
    when account_no = capcon_account_no 
    THEN accountbalance ELSE NULL END)

你也可以为你的储蓄做类似的事情。