java sqlcondition_SQL Join on where condition

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

How do I join these two tables

select m.caseno,

sum(m.BalanceAmount) GOVT from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('a','b','c')

group by m.caseno

OUTPUT CASE | GOVT

select m.caseno,

sum(m.BalanceAmount) MIF from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('d','e')

group by m.caseno

OUTPUT CASE | MIF

I want them to join in a single query CASE | GOVT | MIF

回答1:

If you don't want to touch the existing queries, you can do a FULL OUTER JOIN with them:

select

coalesce(a.caseno, b.caseno) as caseno,

a.govt,

b.mif

from ( -- first query

select m.caseno,

sum(m.BalanceAmount) GOVT from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('a','b','c')

group by m.caseno

) a

full outer join ( -- second query

select m.caseno,

sum(m.BalanceAmount) MIF from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('d','e')

group by m.caseno

) b on a.caseno = b.caseno

The FULL OUTER JOIN is necessary -- as well as the COALESCE() -- to join non-matching rows that may show up in either queries.

回答2:

use case when to show as 2 columns.

select m.caseno,

sum(case when g.alpha in ('d','e') then m.BalanceAmount else 0 end) MIF ,

sum(case when g.alpha in ('a','b','c') then m.BalanceAmount else 0 end) GOVT

from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('a','b','c','d','e')

group by m.caseno

回答3:

you have to perform a full outer join i think. try this.

SELECT *

FROM (

select m.caseno,

sum(m.BalanceAmount) GOVT from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('a','b','c')

group by m.caseno

) AS tab1

FULL OUTER JOIN (

select m.caseno,

sum(m.BalanceAmount) MIF from TABLE1 m

inner join TABLE2 g on m.Code = g.Code

where g.alpha in ('d','e')

group by m.caseno

) AS tab2

ON tab2.caseno = tab1.caseno