select account.*,iteminfo.jhd,(CASE WHEN iteminfo.jhd=1 THEN account.sl as account.jsl WHEN iteminfo.jhd=0 THEN account.sl as account.dsl) from account inner join iteminfo on account.itembm=iteminfo.itembmselect account.*,iteminfo.jhd from account inner join iteminfo on account.itembm=iteminfo.itembm这没错,代表财务表中科目凭证和科目明细通过itembm(科目编码)进行关联,这里完全正确
但我想实现当iteminfo.jhd=1时 account.sl as account.sl1,表示为借方数量,当iteminfo.jhd=0时
account.sl as account.sl2表示为货方数量
但我想实现当iteminfo.jhd=1时 account.sl as account.sl1,表示为借方数量,当iteminfo.jhd=0时
account.sl as account.sl2表示为货方数量
select a.*,b.jhd,(CASE WHEN b.jhd=1 THEN a.sl1 WHEN b.jhd=0 THEN a.sl2 end) as a.sl
from account a inner join iteminfo b on a.itembm=b.itembm
a.*,
b.jhd,
b.jsl,
b.dsl
from account a
join (select
itembm,
jhd,
MAX(CASE WHEN iteminfo.jhd=1 THEN account.sl ELSE 0 END) jsl,
MAX(CASE WHEN iteminfo.jhd=0 THEN account.sl ELSE 0 END) dsl
from iteminfo
group by itembm,jhd
) b
on a.itembm=b.itembm
(CASE i.jhd WHEN 1 THEN a.sl ELSE 0 END) AS jsl,
(CASE i.jhd WHEN 0 THEN a.sl ELSE 0 END) AS dsl
from account a inner join iteminfo i on a.itembm=i.itembm
(CASE i.jhd WHEN 1 THEN a.sl ELSE 0 END) AS jsl,
(CASE i.jhd WHEN 0 THEN a.sl ELSE 0 END) AS dsl
from account a inner join iteminfo i on a.itembm=i.itembm
(CASE i.jhd WHEN 1 THEN a.sl ELSE 0 END) AS jsl,
(CASE i.jhd WHEN 0 THEN a.sl ELSE 0 END) AS dsl
from account a inner join iteminfo i on a.itembm=i.itembm
select A.*,iteminfo.jhd
from iteminfo join
(
select itembm,
sum(CASE WHEN iteminfo.jhd=1 THEN account.sl else 0 end) 'account.jsl',
sum(CASE WHEN iteminfo.jhd=0 THEN account.sl else 0 end) 'account.dsl'
from account
) A
on A.itembm=iteminfo.itembm
from iteminfo join
(
select itembm,
sum(CASE WHEN iteminfo.jhd=1 THEN account.sl else 0 end) 'account.jsl',
sum(CASE WHEN iteminfo.jhd=0 THEN account.sl else 0 end) 'account.dsl'
from account group by itembm
) A
on A.itembm=iteminfo.itembm
-->不好意思,2需要修正。
select
a.*,
b.jhd,
b.jsl,
b.dsl
from account a
join (select
t1.itembm,
t1.jhd,
MAX(CASE WHEN t1.jhd=1 THEN t2.sl ELSE 0 END) jsl,
MAX(CASE WHEN t1.jhd=0 THEN t2.sl ELSE 0 END) dsl
from iteminfo t1
join account t2
on t1.itembm=t2.itembm
group by t1.itembm,t1.jhd
) b
on a.itembm=b.itembm
inner join (select
t1.itembm,
t1.jhd,
sum(CASE WHEN t1.jhd=1 THEN t2.sl ELSE 0 END) jsl,
sum(CASE WHEN t1.jhd=0 THEN t2.sl ELSE 0 END) dsl
from iteminfo t1
join account t2
on t1.itembm=t2.itembm
group by t1.itembm,t1.jhd
) b ON a.itembm=b.itembm
select account.*,iteminfo.jhd,jsl=(CASE WHEN iteminfo.jhd=1 THEN account.sl else 0 end),
dsl=(case when iteminfo.jhd=0 THEN account.sl else 0 end)
from account inner join iteminfo
on account.itembm=iteminfo.itembm
select account.*,iteminfo.jhd,jsl1=(CASE WHEN iteminfo.jhd=1 THEN account.sl else 0 end),
dsl1=(case when iteminfo.jhd=0 THEN account.sl else 0 end)
from account inner join iteminfo
on account.itembm=iteminfo.itembm