根据WHERE条件创建列 [英] Creating Column based on WHERE condition

问题描述

我有以下查询:

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的数量.为了在Ledger中显示它，我必须在应用程序中添加一些额外的代码，以将相应的金额推送到借方"列(如果是tn.CrControl = 7)或贷方"列(如果是tn.DrControl = 7).

the tn.Amount column is the amount of the Journal. To present it in Ledger I've to have some extra code in my Application to push that amount either in Debit column, if tn.CrControl = 7, or Credit column, if tn.DrControl = 7.

是否可以在我的SQL查询中在此处创建DrAmount和CrAmount?

Is it possible to create DrAmount and CrAmount here in my SQL query?

推荐答案

您可以为此使用大小写表达式:

You can use case expressions for this:

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，这使它更短了.

Note that I also rewrote your where clause to use in instead of or, which makes it a little shorter.

这篇关于根据WHERE条件创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！