SQL每日一题(20211112)希望将T1112A和T1112B中的数据进行关联后对发票进行拆分

最新推荐文章于 2024-11-09 16:20:10 发布

time丶sand

最新推荐文章于 2024-11-09 16:20:10 发布

阅读量129

点赞数

分类专栏：每日一练文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124850887

版权

每日一练专栏收录该内容

119 篇文章 17 订阅

订阅专栏

本文详细探讨了如何使用SQL进行数据处理，通过UNION ALL操作合并两个表的数据，并利用窗口函数求和。接着，通过ROW_NUMBER()对数据进行排序，创建了虚拟列来实现收款和发票类型的序列分析。文章展示了如何计算值的变化并预测后续值，适用于财务监控和业务洞察。

摘要由CSDN通过智能技术生成

在这里插入图片描述


with un as (SELECT *, SUM(val) over (partition by name order by ttype) vsum
            FROM t1112a
            UNION ALL
            SELECT *,
                   SUM(val) over (partition by name order by ttype) vsum
            FROM t1112b),
     rnk as (
         SELECT *, ROW_NUMBER() over (partition by name order by vsum)
         FROM un),
     cte as (
         SELECT name,ttype,vsum,
                if(left(ttype, 2) = '收款', lead(ttype) over (partition by name), ttype) val1,
                vsum - lag(vsum, 1, 0) over (partition by name)                        val,
                if(left(ttype, 2) = '发票', lead(ttype) over (partition by name), ttype) val2
         from rnk)
SELECT name, val1, val,val2,vsum, if(left(val2, 2) = '发票', lead(val2) over (partition by name), val2) val2
FROM cte
where val1 is not null
  and val2 is not null