请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需一步,快速开始

Eunky

注册会员

4

主题

10

帖子

88

积分

注册会员

积分
88
Eunky
注册会员   /  发表于:2025-1-21 21:26  /   查看:84  /  回复:2
100金币
本帖最后由 Eunky 于 2025-1-21 21:27 编辑

源数据类似下面这样:
年份,月份,单位,销售额和收款数据



需求:
最终生成这样的表格


怎么实现这个?请求大佬给点思路

附件: 您需要 登录 才可以下载或查看,没有帐号?立即注册

最佳答案

查看完整内容

先看效果: 步骤一、创建按月汇总视图, 视图1 步骤二、创建累计欠款视图,视图2 步骤三、创建横排销售视图,xs 步骤四、创建横排收款视图 sk 步骤五、创建横排欠款视图 qk 步骤六、创建整合视图 视图3 效果: 附: 需要当月欠款的话,增加当月其欠款视图=>创建横排当月欠款视图 dyqk 修改整合视图:

2 个回复

倒序浏览
最佳答案
最佳答案
amtath悬赏达人认证 活字格认证
论坛元老   /  发表于:2025-1-21 21:26:02
来自 2#
本帖最后由 amtath 于 2025-1-22 00:59 编辑

先看效果:


步骤一、创建按月汇总视图, 视图1
  1. SELECT 年份,月份,欠款单位或个人姓名,sum(ifnull(销售额,0)) xse,
  2. sum(ifnull(收款,0)) sk ,sum(ifnull(销售额,0))-sum(ifnull(收款,0)) qk
  3. FROM [销_收明细]
  4. group by 年份,月份,欠款单位或个人姓名
复制代码

步骤二、创建累计欠款视图,视图2
  1. SELECT
  2.     a.年份,
  3.     a.月份,
  4.     a.[欠款单位或个人姓名],
  5.     a.qk dyqk,
  6.     SUM(a.qk) OVER (PARTITION BY a.[欠款单位或个人姓名] ORDER BY a.年份, a.月份) AS qk
  7. FROM
  8.     [视图1] a
复制代码


步骤三、创建横排销售视图,xs
  1. SELECT
  2.     年份,欠款单位或个人姓名,
  3.     SUM(CASE WHEN 月份 = '1' THEN xse ELSE 0 END) AS m1,
  4.     SUM(CASE WHEN 月份 = '2' THEN xse ELSE 0 END) AS m2,
  5.     SUM(CASE WHEN 月份 = '3' THEN xse ELSE 0 END) AS m3,
  6.     SUM(CASE WHEN 月份 = '4' THEN xse ELSE 0 END) AS m4,
  7.     SUM(CASE WHEN 月份 = '5' THEN xse ELSE 0 END) AS m5,
  8.     SUM(CASE WHEN 月份 = '6' THEN xse ELSE 0 END) AS m6,
  9.     SUM(CASE WHEN 月份 = '7' THEN xse ELSE 0 END) AS m7,
  10.     SUM(CASE WHEN 月份 = '8' THEN xse ELSE 0 END) AS m8,
  11.     SUM(CASE WHEN 月份 = '9' THEN xse ELSE 0 END) AS m9,
  12.     SUM(CASE WHEN 月份 = '10' THEN xse ELSE 0 END) AS m10,
  13.     SUM(CASE WHEN 月份 = '11' THEN xse ELSE 0 END) AS m11,
  14.     SUM(CASE WHEN 月份 = '12' THEN xse ELSE 0 END) AS m12
  15. FROM
  16.     [视图1]
  17. GROUP BY
  18.     年份,欠款单位或个人姓名
复制代码


步骤四、创建横排收款视图 sk
  1. SELECT
  2.     年份,欠款单位或个人姓名,
  3.     SUM(CASE WHEN 月份 = '1' THEN sk ELSE 0 END) AS m1,
  4.     SUM(CASE WHEN 月份 = '2' THEN sk ELSE 0 END) AS m2,
  5.     SUM(CASE WHEN 月份 = '3' THEN sk ELSE 0 END) AS m3,
  6.     SUM(CASE WHEN 月份 = '4' THEN sk ELSE 0 END) AS m4,
  7.     SUM(CASE WHEN 月份 = '5' THEN sk ELSE 0 END) AS m5,
  8.     SUM(CASE WHEN 月份 = '6' THEN sk ELSE 0 END) AS m6,
  9.     SUM(CASE WHEN 月份 = '7' THEN sk ELSE 0 END) AS m7,
  10.     SUM(CASE WHEN 月份 = '8' THEN sk ELSE 0 END) AS m8,
  11.     SUM(CASE WHEN 月份 = '9' THEN sk ELSE 0 END) AS m9,
  12.     SUM(CASE WHEN 月份 = '10' THEN sk ELSE 0 END) AS m10,
  13.     SUM(CASE WHEN 月份 = '11' THEN sk ELSE 0 END) AS m11,
  14.     SUM(CASE WHEN 月份 = '12' THEN sk ELSE 0 END) AS m12
  15. FROM
  16.     [视图1]
  17. GROUP BY
  18.     年份,欠款单位或个人姓名
复制代码


步骤五、创建横排欠款视图 qk
  1. SELECT
  2.     年份,欠款单位或个人姓名,
  3.     SUM(CASE WHEN 月份 = '1' THEN qk ELSE 0 END) AS m1,
  4.     SUM(CASE WHEN 月份 = '2' THEN qk ELSE 0 END) AS m2,
  5.     SUM(CASE WHEN 月份 = '3' THEN qk ELSE 0 END) AS m3,
  6.     SUM(CASE WHEN 月份 = '4' THEN qk ELSE 0 END) AS m4,
  7.     SUM(CASE WHEN 月份 = '5' THEN qk ELSE 0 END) AS m5,
  8.     SUM(CASE WHEN 月份 = '6' THEN qk ELSE 0 END) AS m6,
  9.     SUM(CASE WHEN 月份 = '7' THEN qk ELSE 0 END) AS m7,
  10.     SUM(CASE WHEN 月份 = '8' THEN qk ELSE 0 END) AS m8,
  11.     SUM(CASE WHEN 月份 = '9' THEN qk ELSE 0 END) AS m9,
  12.     SUM(CASE WHEN 月份 = '10' THEN qk ELSE 0 END) AS m10,
  13.     SUM(CASE WHEN 月份 = '11' THEN qk ELSE 0 END) AS m11,
  14.     SUM(CASE WHEN 月份 = '12' THEN qk ELSE 0 END) AS m12
  15. FROM
  16.     [视图2]
  17. GROUP BY
  18.     年份,欠款单位或个人姓名
复制代码


步骤六、创建整合视图 视图3
  1. SELECT '销售' lx,1 st,* FROM [xs]
  2. union
  3. SELECT '收款' lx,2 st,* FROM [sk]
  4. union
  5. SELECT '累计欠款' lx,4 st,* FROM [qk]
  6. order by 年份,欠款单位或个人姓名, st
复制代码


效果:




附:
需要当月欠款的话,增加当月其欠款视图=>创建横排当月欠款视图 dyqk
  1. SELECT
  2.     年份,欠款单位或个人姓名,
  3.     SUM(CASE WHEN 月份 = '1' THEN qk ELSE 0 END) AS m1,
  4.     SUM(CASE WHEN 月份 = '2' THEN qk ELSE 0 END) AS m2,
  5.     SUM(CASE WHEN 月份 = '3' THEN qk ELSE 0 END) AS m3,
  6.     SUM(CASE WHEN 月份 = '4' THEN qk ELSE 0 END) AS m4,
  7.     SUM(CASE WHEN 月份 = '5' THEN qk ELSE 0 END) AS m5,
  8.     SUM(CASE WHEN 月份 = '6' THEN qk ELSE 0 END) AS m6,
  9.     SUM(CASE WHEN 月份 = '7' THEN qk ELSE 0 END) AS m7,
  10.     SUM(CASE WHEN 月份 = '8' THEN qk ELSE 0 END) AS m8,
  11.     SUM(CASE WHEN 月份 = '9' THEN qk ELSE 0 END) AS m9,
  12.     SUM(CASE WHEN 月份 = '10' THEN qk ELSE 0 END) AS m10,
  13.     SUM(CASE WHEN 月份 = '11' THEN qk ELSE 0 END) AS m11,
  14.     SUM(CASE WHEN 月份 = '12' THEN qk ELSE 0 END) AS m12
  15. FROM
  16.     [视图1]
  17. GROUP BY
  18.     年份,欠款单位或个人姓名
复制代码

修改整合视图:
  1. SELECT '销售' lx,1 st,* FROM [xs]
  2. union
  3. SELECT '收款' lx,2 st,* FROM [sk]
  4. union
  5. SELECT '当月欠款' lx,3 st,* FROM [dyqk]
  6. union
  7. SELECT '累计欠款' lx,4 st,* FROM [qk]

  8. order by 年份,欠款单位或个人姓名, st
复制代码


本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

评分

参与人数 1金币 +5 收起 理由
Lay.Li + 5 赞一个!

查看全部评分

回复 使用道具 举报
Lay.Li悬赏达人认证 活字格认证
超级版主   /  发表于:2025-1-22 10:20:43
3#
感谢各位大佬的支持~
后边有问题呢欢迎您继续发帖交流
回复 使用道具 举报
您需要登录后才可以回帖 登录 | 立即注册
返回顶部