基于where条件创建列

vql8enpb 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(319)

我有以下疑问：

SELECT 
    tn.Date, b1.Name DrBook, b2.Name CrBook, c1.Name DrControl, 
    c2.Name CrControl, l1.Name DrLedger, l2.Name CrLedger, 
    s1.Name DrSubLedger, s2.Name CrSubLedger, p1.Name DrParty, 
    p2.Name CrParty, m1.Name DrMember, m2.Name CrMember, tn.Amount, 
    tn.Narration 
FROM 
    Transactions tn
LEFT JOIN 
    Books b1 ON b1.Id = tn.DrBook
LEFT JOIN 
    Books b2 ON b2.Id = tn.CrBook
LEFT JOIN 
    ControlLedgers c1 ON c1.Id = tn.DrControl
LEFT JOIN 
    ControlLedgers c2 ON c2.Id = tn.CrControl
LEFT JOIN 
    Ledgers l1 ON l1.Id = tn.DrLedger
LEFT JOIN 
    Ledgers l2 ON l2.Id = tn.CrLedger
LEFT JOIN 
    SubLedgers s1 ON s1.Id = tn.DrSubLedger
LEFT JOIN 
    SubLedgers s2 ON s2.Id = tn.CrSubLedger
LEFT JOIN 
    Parties p1 ON p1.Id = tn.DrParty
LEFT JOIN 
    Parties p2 ON p2.Id = tn.CrParty
LEFT JOIN 
    Members m1 ON m1.Id = tn.DrMember
LEFT JOIN 
    Members m2 ON m2.Id = tn.CrMember
WHERE 
    tn.DrControl = 7 OR tn.CrControl = 7

这个 tn.Amount 列是 Journal . 为了在分类帐中显示它，我必须在我的应用程序中有一些额外的代码，以便在借方列中推送该金额，如果有的话 tn.CrControl = 7 ，或信用栏，如果 tn.DrControl = 7 .
有没有可能创造 DrAmount 以及 CrAmount 在我的sql查询中？

sql Join sqlite select case

来源：https://stackoverflow.com/questions/62359913/creating-column-based-on-where-condition

1条答案

按热度按时间

9q78igpj1#

可以使用大小写表达式：

select 
    ...,
    case when tn.CrControl = 7 then tn.Amount end as debit,
    case when tn.DrControl = 7 then tn.Amount end as credit
from ...
where 7 in (tn.DrControl, tn.CrControl)

注意，我还重写了你的 where 要使用的子句 in 而不是 or ，这使它变短了一点。

赞(0）回复(0）举报 2021-08-09

我来回答

基于where条件创建列

1条答案

相关问题

热门标签

最新问答