时间戳

首页 > 解决方案 > 根据匹配的非无效记录转换无效记录

sql - 根据匹配的非无效记录转换无效记录

问题描述

当某些情况发生时，我必须转换 4 个字段。
必须使用非 VOID（类型 0,2）转换 VOID（类型 = 6）行。每行的事务都是唯一的，VOID 行在 Voidtran 字段中具有原始事务。

这是我的查询：

SELECT   CAST(payhist.number AS CHAR) Transaction
        ,CAST(payhist.descr AS CHAR) TransactionDesc
        ,CAST(payhist.account AS CHAR) Account
        ,CAST(payhist.type AS CHAR) Type
        ,CAST(payhist.voidtran AS CHAR) Voidtran
        ,CAST(servdef.number AS CHAR) ServDefNum
        ,CAST(servdef.descr AS CHAR) ServDefDesc
        ,CAST(LEFT(list_charge.descr,5) AS CHAR) ChargeType
        ,CAST(list_charge.descr AS CHAR) ChargeTypeDesc
FROM billmax.payhist 
    LEFT JOIN monthlysale ON  monthlysale.payhist = payhist.number
    LEFT JOIN servdef ON payhist.servdef = servdef.number 
    LEFT JOIN lists AS list_charge ON list_charge.value = payhist.charge_type AND list_charge.list='chargetypes'
WHERE payhist.type IN (0,2,6) 
AND monthlysale.bookdate BETWEEN '2020-12-01' AND NOW()
AND monthlysale.amount <>0
AND payhist.number IN(9751739,9729411)

以下是数据的样子以及我希望结果的样子： 在此处输入图像描述

我想我需要构建一个 UNION 查询 - 一部分用于类型 0 或 2，一部分用于类型 6，但我不知道如何更改这 4 个字段。非常感谢所有帮助！

标签： sql

解决方案

如果我理解正确，您想在值为 NULL 时“传播”值。这使用COALESCE()和窗口函数：

SELECT COALESCE(payhist.number, MAX(payhist.number) OVER (PARTITION BY payhist.account)) as Transaction
       payhist.descras as TransactionDesc
       payhist.account as Account
       COALESCE(payhist.type, MAX(payhist.type) OVER (PARTITION BY payhist.account) as Type,
      . . .
FROM billmax.payhist JOIN
     monthlysale
     ON  monthlysale.payhist = payhist.number LEFT JOIN
     servdef
     ON payhist.servdef = servdef.number LEFT JOIN
     lists AS list_charge
     ON list_charge.value = payhist.charge_type AND list_charge.list = 'chargetypes'
WHERE payhist.type IN (0,2,6) AND
      monthlysale.bookdate BETWEEN '2020-12-01' AND NOW() AND
      monthlysale.amount <> 0 AND
      payhist.number IN (9751739, 9729411) ;

我不知道您为什么要将值转换为字符串（尤其是似乎已经是字符串的值），所以我只是删除了该逻辑。

推荐阅读