时间戳

首页 > 解决方案 > 如何在sqlserver2014中使用case将两列数据放入一列？

sql - 如何在sqlserver2014中使用case将两列数据放入一列？

问题描述

如果 2018 年的销售额 = 0，我需要将 2019 年的销售额放入一个名为新的单独列中。这是我的代码供您参考

SELECT 
    ISNULL(sp.Telephone,'No Sales Employee'),
    od.CardCode,
    od.CardName,
    Sum(case when c.U_ItemStatus = '2' then od.DocTotal else 0 end) as Sold2018,
    Sum(case when c.U_ItemStatus = '1' then od.DocTotal else 0 end) as Sold2019,
    (select od.DocTotal where c.U_ItemStatus = '2' ) as new
FROM
    ORDR od
Left Join OSLP sp
    On od.SlpCode=sp.SlpCode
left join RDR1 a on a.DocEntry = od.DocEntry
left join OITM b on b.ItemCode = a.ItemCode
left Join OITB c on c.ItmsGrpCod = b.ItmsGrpCod
Group By
    sp.Telephone,
    od.CardCode,
    od.CardName,
    c.U_ItemStatus,
    od.DocTotal

标签： sqlsql-serversql-server-2014

解决方案

使用CASE语句并注意您需要Sold2018在条件中使用整个表达式而不是alias

CASE WHEN Sum(case when c.U_ItemStatus = '2' then od.DocTotal else 0 end) = 0 
     THEN Sum(case when c.U_ItemStatus = '1' then od.DocTotal else 0 end)
     END  AS New

推荐阅读