Sql语法高级应用之五：使用存储过程实现对明细多层次统计

前言

前面章节我们讲到了存储过程的基础用法，本章则将一个在项目中实际应用的场景。

在项目中经常会存在这样的需求，例如需要对明细列表进行按组、按级别、按人等进行统计，如果在附带列表的查询条件，又如何实现呢？

一般的思路是：先拿到明细数据，然后分别按照组，级别，人进行统计得到三个集合。

正确的做法应该是：客户需要什么维度的统计就获取当前维度的列表即可，在程序中要尽可能避免用不到的逻辑处理。

下面附上我的解决方案：

方案

我的方案是：利用存储过程将查询条件与统计维度的标示通过参数传递给存储过程，存储过程则负责返回统计后的结果。

下面附上实际案例：

存储过程代码

USE Wot_Inventory;

GO
-- 判断要创建的存储过程名是否存在
IF EXISTS
(
    SELECT *
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[Wot_Inventory].[dbo].[sp_nodeliverystatistics]')
          AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
    -- 删除存储过程
    DROP PROC [sp_nodeliverystatistics];
GO
CREATE PROC [sp_nodeliverystatistics]
(
    @ListName NVARCHAR(16), --列表名称
    @Nav NVARCHAR(16),      --导航名称
    @Where VARCHAR(MAX),    --查询条件
    @msg NVARCHAR(200) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sqlStr NVARCHAR(MAX);

    BEGIN TRY
        IF @ListName <> ''
           AND @ListName IS NOT NULL
           AND @Nav <> ''
        BEGIN

            IF (@ListName = 'Agreed')
            BEGIN
                SET @sqlStr
                    = N'SELECT ' + @Nav
                      + ',COUNT(Id) Number,SUM(Receivable) TotalReceivable,AVG(Receivable) AvgReceivable,SUM(AgencyFund) TotalAgencyFund,AVG(Receivable) AvgAgencyFund FROM Wot_Inventory.dbo.NoDeliveryByDetailView WHERE 1 = 1';
                EXEC (@sqlStr + @Where + N' GROUP BY ' + @Nav + '');
                SET @msg = '列表统计成功';
            END;
            ELSE IF (@ListName = 'Immediate')
            BEGIN
                SET @sqlStr
                    = N'SELECT ' + @Nav
                      + ',COUNT(Id) Number,SUM(Receivable) TotalReceivable,SUM(AgencyFund) TotalAgencyFund,SUM(InsurreValue) TotalInsurreValue,SUM(FragileInsurreValue) TotalFragileValue FROM Wot_Inventory.dbo.NoDeliveryByDetailView WHERE 1 = 1';
                EXEC (@sqlStr + @Where + N' GROUP BY ' + @Nav + '');
                SET @msg = '列表统计成功';
            END;
            ELSE
            BEGIN
                SET @msg = '传递的参数不正确';
            END;

        END;
        ELSE
        BEGIN
            SET @msg = '传递的参数不正确';
        END;
    END TRY
    BEGIN CATCH
        SET @msg = ERROR_MESSAGE();
        PRINT @msg;
    END CATCH;
    PRINT @msg;
END;
GO

程序代码

 List<AgreedStatistics> dtoResult1 = new List<AgreedStatistics>();
            List<AgreedStatistics> dtoResult2 = new List<AgreedStatistics>();
            List<AgreedStatistics> dtoResult3 = new List<AgreedStatistics>();
            List<AgreedStatistics> dtoResult4 = new List<AgreedStatistics>();
            List<AgreedStatistics> dtoResult5 = new List<AgreedStatistics>();

            //按预约
            string sql = "EXEC Wot_Inventory.dbo.sp_nodeliverystatistics @ListName, @Nav, @Where, @msg OUT";
            var sqlwhere = "";
            sqlwhere = SetSqlWhere(sqlwhere, request, user, "Dtl");
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ListName","Agreed"),
                new SqlParameter("@Nav","AgreedDate"),
                new SqlParameter("@Where",sqlwhere),
                new SqlParameter("@msg",System.Data.SqlDbType.NVarChar,200)
            };
            parms[3].Direction = System.Data.ParameterDirection.Output;

            dtoResult1 = deliverRepository.ExecuteSql<AgreedStatistics>(sql, parms).OrderByDescending(t => t.TotalReceivable).ToList();
            msg = parms[3].Value.ToString();
            dtoResult1.ForEach(t =>
            {
                t.Id = index1++;
            });

            //销售组
            string sql2 = "EXEC Wot_Inventory.dbo.sp_nodeliverystatistics @ListName, @Nav, @Where, @msg OUT";
            var sqlwhere2 = "";
            sqlwhere2 = SetSqlWhere(sqlwhere2, request, user, "Dtl");
            SqlParameter[] parms2 = new SqlParameter[]
            {
                new SqlParameter("@ListName","Agreed"),
                new SqlParameter("@Nav","SalesGroupName"),
                new SqlParameter("@Where",sqlwhere2),
                new SqlParameter("@msg",System.Data.SqlDbType.NVarChar,200)
            };
            parms2[3].Direction = System.Data.ParameterDirection.Output;

            dtoResult2 = deliverRepository.ExecuteSql<AgreedStatistics>(sql2, parms2).OrderByDescending(t => t.TotalReceivable).ToList();
            msg = parms2[3].Value.ToString();
            dtoResult2.ForEach(t =>
            {
                t.Id = index2++;
            });

前端效果

在当前案例中，我使用了缓存，在查询明细的同时，将所有维度的统计缓存起来，因为考虑到这个明细的列表不会产生太多的数据。这也是一种解决问题的思维，具体按实际情况而定。

PS:欢迎扫描下方二维码或点击链接，加入QQ群