本帖最后由 amtath 于 2025-1-22 00:59 编辑
先看效果:
步骤一、创建按月汇总视图, 视图1
- SELECT 年份,月份,欠款单位或个人姓名,sum(ifnull(销售额,0)) xse,
- sum(ifnull(收款,0)) sk ,sum(ifnull(销售额,0))-sum(ifnull(收款,0)) qk
- FROM [销_收明细]
- group by 年份,月份,欠款单位或个人姓名
复制代码
步骤二、创建累计欠款视图,视图2
- SELECT
- a.年份,
- a.月份,
- a.[欠款单位或个人姓名],
- a.qk dyqk,
- SUM(a.qk) OVER (PARTITION BY a.[欠款单位或个人姓名] ORDER BY a.年份, a.月份) AS qk
- FROM
- [视图1] a
复制代码
步骤三、创建横排销售视图,xs
- SELECT
- 年份,欠款单位或个人姓名,
- SUM(CASE WHEN 月份 = '1' THEN xse ELSE 0 END) AS m1,
- SUM(CASE WHEN 月份 = '2' THEN xse ELSE 0 END) AS m2,
- SUM(CASE WHEN 月份 = '3' THEN xse ELSE 0 END) AS m3,
- SUM(CASE WHEN 月份 = '4' THEN xse ELSE 0 END) AS m4,
- SUM(CASE WHEN 月份 = '5' THEN xse ELSE 0 END) AS m5,
- SUM(CASE WHEN 月份 = '6' THEN xse ELSE 0 END) AS m6,
- SUM(CASE WHEN 月份 = '7' THEN xse ELSE 0 END) AS m7,
- SUM(CASE WHEN 月份 = '8' THEN xse ELSE 0 END) AS m8,
- SUM(CASE WHEN 月份 = '9' THEN xse ELSE 0 END) AS m9,
- SUM(CASE WHEN 月份 = '10' THEN xse ELSE 0 END) AS m10,
- SUM(CASE WHEN 月份 = '11' THEN xse ELSE 0 END) AS m11,
- SUM(CASE WHEN 月份 = '12' THEN xse ELSE 0 END) AS m12
- FROM
- [视图1]
- GROUP BY
- 年份,欠款单位或个人姓名
复制代码
步骤四、创建横排收款视图 sk
- SELECT
- 年份,欠款单位或个人姓名,
- SUM(CASE WHEN 月份 = '1' THEN sk ELSE 0 END) AS m1,
- SUM(CASE WHEN 月份 = '2' THEN sk ELSE 0 END) AS m2,
- SUM(CASE WHEN 月份 = '3' THEN sk ELSE 0 END) AS m3,
- SUM(CASE WHEN 月份 = '4' THEN sk ELSE 0 END) AS m4,
- SUM(CASE WHEN 月份 = '5' THEN sk ELSE 0 END) AS m5,
- SUM(CASE WHEN 月份 = '6' THEN sk ELSE 0 END) AS m6,
- SUM(CASE WHEN 月份 = '7' THEN sk ELSE 0 END) AS m7,
- SUM(CASE WHEN 月份 = '8' THEN sk ELSE 0 END) AS m8,
- SUM(CASE WHEN 月份 = '9' THEN sk ELSE 0 END) AS m9,
- SUM(CASE WHEN 月份 = '10' THEN sk ELSE 0 END) AS m10,
- SUM(CASE WHEN 月份 = '11' THEN sk ELSE 0 END) AS m11,
- SUM(CASE WHEN 月份 = '12' THEN sk ELSE 0 END) AS m12
- FROM
- [视图1]
- GROUP BY
- 年份,欠款单位或个人姓名
复制代码
步骤五、创建横排欠款视图 qk
- SELECT
- 年份,欠款单位或个人姓名,
- SUM(CASE WHEN 月份 = '1' THEN qk ELSE 0 END) AS m1,
- SUM(CASE WHEN 月份 = '2' THEN qk ELSE 0 END) AS m2,
- SUM(CASE WHEN 月份 = '3' THEN qk ELSE 0 END) AS m3,
- SUM(CASE WHEN 月份 = '4' THEN qk ELSE 0 END) AS m4,
- SUM(CASE WHEN 月份 = '5' THEN qk ELSE 0 END) AS m5,
- SUM(CASE WHEN 月份 = '6' THEN qk ELSE 0 END) AS m6,
- SUM(CASE WHEN 月份 = '7' THEN qk ELSE 0 END) AS m7,
- SUM(CASE WHEN 月份 = '8' THEN qk ELSE 0 END) AS m8,
- SUM(CASE WHEN 月份 = '9' THEN qk ELSE 0 END) AS m9,
- SUM(CASE WHEN 月份 = '10' THEN qk ELSE 0 END) AS m10,
- SUM(CASE WHEN 月份 = '11' THEN qk ELSE 0 END) AS m11,
- SUM(CASE WHEN 月份 = '12' THEN qk ELSE 0 END) AS m12
- FROM
- [视图2]
- GROUP BY
- 年份,欠款单位或个人姓名
复制代码
步骤六、创建整合视图 视图3
- SELECT '销售' lx,1 st,* FROM [xs]
- union
- SELECT '收款' lx,2 st,* FROM [sk]
- union
- SELECT '累计欠款' lx,4 st,* FROM [qk]
- order by 年份,欠款单位或个人姓名, st
复制代码
效果:
附:
需要当月欠款的话,增加当月其欠款视图=>创建横排当月欠款视图 dyqk
- SELECT
- 年份,欠款单位或个人姓名,
- SUM(CASE WHEN 月份 = '1' THEN qk ELSE 0 END) AS m1,
- SUM(CASE WHEN 月份 = '2' THEN qk ELSE 0 END) AS m2,
- SUM(CASE WHEN 月份 = '3' THEN qk ELSE 0 END) AS m3,
- SUM(CASE WHEN 月份 = '4' THEN qk ELSE 0 END) AS m4,
- SUM(CASE WHEN 月份 = '5' THEN qk ELSE 0 END) AS m5,
- SUM(CASE WHEN 月份 = '6' THEN qk ELSE 0 END) AS m6,
- SUM(CASE WHEN 月份 = '7' THEN qk ELSE 0 END) AS m7,
- SUM(CASE WHEN 月份 = '8' THEN qk ELSE 0 END) AS m8,
- SUM(CASE WHEN 月份 = '9' THEN qk ELSE 0 END) AS m9,
- SUM(CASE WHEN 月份 = '10' THEN qk ELSE 0 END) AS m10,
- SUM(CASE WHEN 月份 = '11' THEN qk ELSE 0 END) AS m11,
- SUM(CASE WHEN 月份 = '12' THEN qk ELSE 0 END) AS m12
- FROM
- [视图1]
- GROUP BY
- 年份,欠款单位或个人姓名
复制代码
修改整合视图:
- SELECT '销售' lx,1 st,* FROM [xs]
- union
- SELECT '收款' lx,2 st,* FROM [sk]
- union
- SELECT '当月欠款' lx,3 st,* FROM [dyqk]
- union
- SELECT '累计欠款' lx,4 st,* FROM [qk]
- order by 年份,欠款单位或个人姓名, st
复制代码
|