求一个SQL----关于应收货款的金额年度统计表

[IamRobat]

是这样,用户需要统计出一个年度中每个月,每个客户的未结款项,
把每个月的金额填到那个月份里?
达到视图2的效果

详细说明:
1.客户是月结的,每月应付只有一笔,视图1已实现汇总功能得出的结果.
2.如果客户在该付的款的那个月没付,后面的月份的汇总那里就填那个月的金额.
3.现在是12月,所以图2的4月后都是3月未还的2600



1.视图1

/*客户月度该付金额汇总表*/
单号                        客户编号  名称      应付金额   应收日期     已付金额  支付日期    
112101	C013	公司A	12800	 2014/2/21	12800	2014/12/19	
112101	C013	公司A	2600        2015/3/21     0               0		
....	

2.要求的效果图
客户名 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
--------------------------------------------------------------------------------------------------------------------------------------
公司A 12800 2600 2600 2600 2600 2600 2600 2600 2600 2600 2600 2600
.....

[dovei]

这个很简单的。先按月汇总，再行转列就成了！

[dovei]

目测你是做ERP的！在哪里？

[Tiger_Zhao]

WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-02-21',12800 UNION ALL
SELECT '公司A',2600,'2015-03-21',0 UNION ALL
SELECT '公司A',3600,'2015-04-21',0 
)
,c AS (
    SELECT DISTINCT 名称 FROM view1
)
,t1 AS (
    SELECT c.名称,
           m.month,
           v.应付金额,
           ISNULL(v.已付金额,-1) 已付金额
      FROM c
      JOIN (SELECT number month
              FROM master..spt_values
             WHERE type = 'p'
               AND number BETWEEN 1 AND 12
           ) m
        ON 1=1
 LEFT JOIN view1 v
        ON v.名称 = c.名称
       AND MONTH(v.应收日期) = m.month
)
,t2 AS (
    SELECT * FROM t1 WHERE month = 1
    UNION ALL
    SELECT m2.名称,
           m2.month,
           CASE WHEN m1.已付金额 = 0 THEN
                m1.应付金额
           ELSE
                m2.应付金额
           END,
           CASE WHEN m1.已付金额 = 0 THEN
                0
           ELSE
                m2.已付金额
           END
      FROM t2 m1
      JOIN t1 m2
        ON m2.名称 = m1.名称
       AND m2.month = m1.month+1
)
SELECT *
  FROM (
        SELECT 名称,
               Convert(varchar(11),month)+'月' col,
               应付金额
          FROM t2
       ) t
 PIVOT (
        MAX(应付金额)
        FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
                    [7月],[8月],[9月],[10月],[11月],[12月])
       ) p

名称    1月   2月   3月   4月   5月   6月   7月    8月  9月  10月  11月  12月
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
公司A  NULL 12800  2600  2600  2600  2600  2600  2600  2600  2600  2600  2600

[IamRobat]

引用 2 楼 dovei 的回复:

目测你是做ERP的！在哪里？

在深圳做开发的.. 呵呵

[IamRobat]

引用 3 楼 Tiger_Zhao 的回复:

WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-02-21',12800 UNION ALL
SELECT '公司A',2600,'2015-03-21',0 UNION ALL
SELECT '公司A',3600,'2015-04-21',0 
)
,c AS (
    SELECT DISTINCT 名称 FROM view1
)
,t1 AS (
    SELECT c.名称,
           m.month,
           v.应付金额,
           ISNULL(v.已付金额,-1) 已付金额
      FROM c
      JOIN (SELECT number month
              FROM master..spt_values
             WHERE type = 'p'
               AND number BETWEEN 1 AND 12
           ) m
        ON 1=1
 LEFT JOIN view1 v
        ON v.名称 = c.名称
       AND MONTH(v.应收日期) = m.month
)
,t2 AS (
    SELECT * FROM t1 WHERE month = 1
    UNION ALL
    SELECT m2.名称,
           m2.month,
           CASE WHEN m1.已付金额 = 0 THEN
                m1.应付金额
           ELSE
                m2.应付金额
           END,
           CASE WHEN m1.已付金额 = 0 THEN
                0
           ELSE
                m2.已付金额
           END
      FROM t2 m1
      JOIN t1 m2
        ON m2.名称 = m1.名称
       AND m2.month = m1.month+1
)
SELECT *
  FROM (
        SELECT 名称,
               Convert(varchar(11),month)+'月' col,
               应付金额
          FROM t2
       ) t
 PIVOT (
        MAX(应付金额)
        FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
                    [7月],[8月],[9月],[10月],[11月],[12月])
       ) p

名称    1月   2月   3月   4月   5月   6月   7月    8月  9月  10月  11月  12月
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
公司A  NULL 12800  2600  2600  2600  2600  2600  2600  2600  2600  2600  2600

谢谢,但为什么代码我转正后,出现以下报错呢? 消息 240，级别 16，状态 1，第 1 行 在递归查询 "t2" 的列 "PayCost" 中，定位点类型和递归部分的类型不匹配。

WITH c AS (
    SELECT DISTINCT ClientName FROM PayPlanView
)
,t1 AS (
    SELECT c.ClientName,
           m.month,
           v.PlanCost,
           ISNULL(v.PayCost,-1) PayCost
      FROM c
      JOIN (SELECT number month
              FROM master..spt_values
             WHERE type = 'p'
               AND number BETWEEN 1 AND 12
           ) m
        ON 1=1
 LEFT JOIN PayPlanView v
        ON v.ClientName = c.ClientName
       AND MONTH(v.PlanDate) = m.month
)
,t2 AS (
    SELECT * FROM t1 WHERE month = 1
    UNION ALL
    SELECT m2.ClientName,
           m2.month,
           CASE WHEN m1.PayCost = 0 THEN
                m1.PlanCost
           ELSE
                m2.PlanCost
           END,
           CASE WHEN m1.PayCost = 0 THEN
                0
           ELSE
                m2.PayCost
           END
      FROM t2 m1
      JOIN t1 m2
        ON m2.ClientName = m1.ClientName
       AND m2.month = m1.month+1
)
SELECT *
  FROM (
        SELECT ClientName,
               Convert(varchar(11),month)+'月' col,
               PlanCost
          FROM t2
       ) t
 PIVOT (
        MAX(PlanCost)
        FOR col IN ([1月],[2月],[3月],[4月],[5月],[6月],
                    [7月],[8月],[9月],[10月],[11月],[12月])
       ) p

[IamRobat]

引用 6 楼 Tiger_Zhao 的回复:

把 t2 中递归部分返回的 0 显式转换成和 PayConst 一样的类型。

可以了,谢谢,但出现了一个问题,当已付是跨年的时候,前面本来应该是空白的,也部都成了实数, 跨年的时候,不算后面的,只算今年内的 当如下数据测试时,前面1~11月,都被变成了260000,而且12800也没有显示在12月份.:

WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-12-21',12800 UNION ALL
SELECT '公司A',260000,'2015-1-20',0 
)
结果:
名称	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月
公司A	260000	260000	260000	260000	260000	260000	260000	260000	260000	260000	260000	260000

如何能够实现让前面的空白 ,如下: 名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 公司A 12800

[IamRobat]

引用 6 楼 Tiger_Zhao 的回复:

把 t2 中递归部分返回的 0 显式转换成和 PayConst 一样的类型。

知道了,从数据源过滤掉年份,谢谢

[Tiger_Zhao]

对啊。这个只处理一年12个月的，必须一开始就按年过滤。

[Tiger_Zhao]

把 t2 中递归部分返回的 0 显式转换成和 PayConst 一样的类型。

[一头大葱]

这里http://datamachine.iteye.com/admin/blogs/2217205 讨论了JAVA+SQL下的这类行转列实现，也涉及到这个问题，写法要简单许多

[gw6328]

借用前面老兄数据

WITH view1(名称,应付金额,应收日期,已付金额) as (
SELECT '公司A',12800,'2014-02-21',12800 UNION ALL
SELECT '公司A',2600,'2015-03-21',0 UNION ALL
SELECT '公司A',3600,'2015-04-21',0 
)
select 名称,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] from 
(
	select 名称,应付金额-已付金额 as m,MONTH(应收日期) as mm from view1
) a
pivot (sum(m) for  mm in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) pt

/*
名称    1           2           3           4           5           6           7           8           9           10          11          12
----- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
公司A   NULL        0           2600        3600        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
*/

[gw6328]

如果要跨年把时间那里改成 convert(varchar(7),应收日期,23)