摘要：基于SQLServer宽表，通过UNPIVOT与条件聚合，构建客户回款统计视图，计算当月和累计回款率。

SQLServer客户回款统计视图

      构建一个视图，用于统计客户和业务员的回款情况，包括当月应回款、实回款、回款率及累计应回款、实回款、回款率。

视图定义

CREATE VIEW [客户回款统计视图]
AS
WITH 应收明细 AS (
    SELECT 
        [客户名称],
        [业务员],
        YEAR([到期日期]) AS 应收年,
        MONTH([到期日期]) AS 应收月,
        [货款金额] AS 应收金额,
        0 AS 实收金额
    FROM [收款记录]

    UNION ALL

    SELECT 
        [客户名称],
        [业务员],
        YEAR(CASE WHEN 收款序号 = 1 THEN [收款1_日期]
                 WHEN 收款序号 = 2 THEN [收款2_日期]
                 WHEN 收款序号 = 3 THEN [收款3_日期]
                 WHEN 收款序号 = 4 THEN [收款4_日期]
                 WHEN 收款序号 = 5 THEN [收款5_日期]
                 WHEN 收款序号 = 6 THEN [收款6_日期]
                 WHEN 收款序号 = 7 THEN [收款7_日期]
                 WHEN 收款序号 = 8 THEN [收款8_日期]
                 WHEN 收款序号 = 9 THEN [收款9_日期]
                 WHEN 收款序号 = 10 THEN [收款10_日期]
            END) AS 收款年,
        MONTH(CASE WHEN 收款序号 = 1 THEN [收款1_日期]
                  WHEN 收款序号 = 2 THEN [收款2_日期]
                  WHEN 收款序号 = 3 THEN [收款3_日期]
                  WHEN 收款序号 = 4 THEN [收款4_日期]
                  WHEN 收款序号 = 5 THEN [收款5_日期]
                  WHEN 收款序号 = 6 THEN [收款6_日期]
                  WHEN 收款序号 = 7 THEN [收款7_日期]
                  WHEN 收款序号 = 8 THEN [收款8_日期]
                  WHEN 收款序号 = 9 THEN [收款9_日期]
                  WHEN 收款序号 = 10 THEN [收款10_日期]
             END) AS 收款月,
        0 AS 应收金额,
        CASE WHEN 收款序号 = 1 THEN [收款1_金额]
             WHEN 收款序号 = 2 THEN [收款2_金额]
             WHEN 收款序号 = 3 THEN [收款3_金额]
             WHEN 收款序号 = 4 THEN [收款4_金额]
             WHEN 收款序号 = 5 THEN [收款5_金额]
             WHEN 收款序号 = 6 THEN [收款6_金额]
             WHEN 收款序号 = 7 THEN [收款7_金额]
             WHEN 收款序号 = 8 THEN [收款8_金额]
             WHEN 收款序号 = 9 THEN [收款9_金额]
             WHEN 收款序号 = 10 THEN [收款10_金额]
        END AS 实收金额
    FROM (
        SELECT [ID], [客户名称], [业务员],
               [收款1_日期], [收款1_金额],
               [收款2_日期], [收款2_金额],
               [收款3_日期], [收款3_金额],
               [收款4_日期], [收款4_金额],
               [收款5_日期], [收款5_金额],
               [收款6_日期], [收款6_金额],
               [收款7_日期], [收款7_金额],
               [收款8_日期], [收款8_金额],
               [收款9_日期], [收款9_金额],
               [收款10_日期], [收款10_金额]
        FROM [收款记录]
    ) AS SRC
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS SEQ(收款序号)
),
汇总数据 AS (
    SELECT 
        [客户名称],
        [业务员],
        应收年 * 100 + 应收月 AS 年月,
        SUM(应收金额) AS 累计应回,
        SUM(实收金额) AS 累计实回,
        SUM(CASE WHEN YEAR(到期日期) = 应收年 AND MONTH(到期日期) = 应收月 THEN 应收金额 ELSE 0 END) AS 当月应回,
        SUM(CASE WHEN YEAR(到期日期) = 应收年 AND MONTH(到期日期) = 应收月 THEN 实收金额 ELSE 0 END) AS 当月实回
    FROM (
        SELECT 
            A.[客户名称],
            A.[业务员],
            A.应收年,
            A.应收月,
            A.应收金额,
            A.实收金额,
            B.[到期日期]
        FROM 应收明细 A
        LEFT JOIN [收款记录] B ON A.[客户名称] = B.[客户名称] AND A.应收金额 = B.[货款金额]
    ) T
    GROUP BY [客户名称], [业务员], 应收年 * 100 + 应收月
)
SELECT 
    [客户名称] AS 客户,
    [业务员],
    年月,
    当月应回,
    当月实回,
    CASE WHEN 当月应回 = 0 THEN NULL ELSE CAST(当月实回 * 100.0 / 当月应回 AS DECIMAL(10,2)) END AS 当月回款率,
    累计应回,
    累计实回,
    CASE WHEN 累计应回 = 0 THEN NULL ELSE CAST(累计实回 * 100.0 / 累计应回 AS DECIMAL(10,2)) END AS 累计回款率
FROM 汇总数据;

功能描述

      视图功能包括：

• 计算客户和业务员的当月应回款和实回款。
• 计算客户和业务员的累计应回款和实回款。
• 计算当月回款率和累计回款率。
• 支持按年月维度筛选和排序。

示例查询

SELECT * FROM [客户回款统计视图]
WHERE 年月 = 202504
ORDER BY 累计回款率 DESC;