如何从SQL Server单表提取按公司分组的分渠道销售统计数据

注意事项：

1. 以下代码默认BookedDate字段存储格式为yyyy-MM-dd类可直接转换为日期的字符串格式，若实际存储格式不同，请调整CONVERT部分的日期样式参数
2. 建表语句中支付方式字段名为PaymenthMethod（拼写多一个h），代码已沿用该命名，若实际字段名无多余h请自行修改
3. 代码中@SelectedMonth为指定统计月份的自定义参数，格式为yyyy-MM，可根据需求替换为对应年月
4. 线上/线下支付的判断条件需根据实际库中PaymenthMethod的枚举值调整，示例默认现金类支付为线下支付，其余为线上支付

方案1：分渠道独立查询 #

enduser渠道查询语句 #

DECLARE @SelectedMonth nvarchar(7) = '2024-05';

SELECT 
    CompanyName,
    SUM(Cost) AS TotalSales,
    SUM(CASE 
            WHEN CONVERT(date, BookedDate, 23) >= DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1)
            AND CONVERT(date, BookedDate, 23) < DATEADD(month,1,DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1))
        THEN Cost ELSE 0 END) AS SalesOnSelectedMonth,
    SUM(CASE WHEN PaymenthMethod NOT IN ('cash','现金') THEN Cost ELSE 0 END) AS SalesViaOnline,
    SUM(CASE WHEN PaymenthMethod IN ('cash','现金') THEN Cost ELSE 0 END) AS [SalesOffline(cash)]
FROM test
WHERE Source = 'enduser'
GROUP BY CompanyName
ORDER BY CompanyName;

admin渠道查询语句 #

仅需调整WHERE条件的Source过滤值即可：

DECLARE @SelectedMonth nvarchar(7) = '2024-05';

SELECT 
    CompanyName,
    SUM(Cost) AS TotalSales,
    SUM(CASE 
            WHEN CONVERT(date, BookedDate, 23) >= DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1)
            AND CONVERT(date, BookedDate, 23) < DATEADD(month,1,DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1))
        THEN Cost ELSE 0 END) AS SalesOnSelectedMonth,
    SUM(CASE WHEN PaymenthMethod NOT IN ('cash','现金') THEN Cost ELSE 0 END) AS SalesViaOnline,
    SUM(CASE WHEN PaymenthMethod IN ('cash','现金') THEN Cost ELSE 0 END) AS [SalesOffline(cash)]
FROM test
WHERE Source = 'admin'
GROUP BY CompanyName
ORDER BY CompanyName;

方案2：单查询同时输出两个渠道所有统计数据 #

如果需要每家企业一行同时展示两个渠道的全部指标，可使用条件聚合实现：

DECLARE @SelectedMonth nvarchar(7) = '2024-05';

SELECT 
    CompanyName,
    -- enduser渠道指标
    SUM(CASE WHEN Source = 'enduser' THEN Cost ELSE 0 END) AS EndUser_TotalSales,
    SUM(CASE 
            WHEN Source = 'enduser'
            AND CONVERT(date, BookedDate, 23) >= DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1)
            AND CONVERT(date, BookedDate, 23) < DATEADD(month,1,DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1))
        THEN Cost ELSE 0 END) AS EndUser_SalesOnSelectedMonth,
    SUM(CASE WHEN Source = 'enduser' AND PaymenthMethod NOT IN ('cash','现金') THEN Cost ELSE 0 END) AS EndUser_SalesViaOnline,
    SUM(CASE WHEN Source = 'enduser' AND PaymenthMethod IN ('cash','现金') THEN Cost ELSE 0 END) AS [EndUser_SalesOffline(cash)],
    -- admin渠道指标
    SUM(CASE WHEN Source = 'admin' THEN Cost ELSE 0 END) AS Admin_TotalSales,
    SUM(CASE 
            WHEN Source = 'admin'
            AND CONVERT(date, BookedDate, 23) >= DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1)
            AND CONVERT(date, BookedDate, 23) < DATEADD(month,1,DATEFROMPARTS(LEFT(@SelectedMonth,4), RIGHT(@SelectedMonth,2), 1))
        THEN Cost ELSE 0 END) AS Admin_SalesOnSelectedMonth,
    SUM(CASE WHEN Source = 'admin' AND PaymenthMethod NOT IN ('cash','现金') THEN Cost ELSE 0 END) AS Admin_SalesViaOnline,
    SUM(CASE WHEN Source = 'admin' AND PaymenthMethod IN ('cash','现金') THEN Cost ELSE 0 END) AS [Admin_SalesOffline(cash)]
FROM test
GROUP BY CompanyName
ORDER BY CompanyName;

内容的提问来源于stack exchange，提问作者StupidDeveloper