时间戳

首页 > 解决方案 > 根据 WHERE 条件创建列

sql - 根据 WHERE 条件创建列

问题描述

我有以下查询：

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。

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

标签： sqlsqlitejoinselectcase

解决方案

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)

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

推荐阅读