博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
With递归循环实现like功能(性能优化)
阅读量：5043 次
发布时间：2019-06-12

本文共 21204 字，大约阅读时间需要 70 分钟。

函数:

CREATE FUNCTION [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40] ( @DepID INT )   

RETURNS TABLE   
AS   
RETURN   
    (   
WITH    data   
          AS ( SELECT   dt.DepID ,   
                        dt.DepFatherID   
               FROM     dbo.SysDepartment dt  WITH (NOLOCK)  
               WHERE    dt.DepDelState = 1   
                        AND ( @DepID = -1   
                              OR    
                              dt.DepID = @DepID   
                            )   
               UNION ALL   
               SELECT   A1.DepID ,   
                        A1.DepFatherID   
               FROM     dbo.SysDepartment A1    WITH ( INDEX ( 1 ) )   
                        INNER JOIN data b ON A1.DepFatherID = b.DepID   
               WHERE    A1.DepDelState = 1   
             )   
    SELECT  DepID DataID,   
            DepFatherID   
    FROM    data   
)

应用于存储过程(替代了like语句):

/**********************************                                       

--Author:YW                                       
--CreateDate:2012-12-31                                     
--Description:相关区域动态分析                                       
***********************************/                 
CREATE PROCEDURE [dbo].[UP_XA_DynamicReport_AboutDepReport]   
    @CurrentUserID INT ,   
    @DeptLevel INT ,   
    @DepID INT ,   
    @SaleUserID INT ,   
    @CusType SMALLINT ,   
    @CustomerID INT ,   
    @ProTypeID INT ,   
    @ProductID INT ,   
    @HospitalID INT ,   
    @DateType INT ,--统计区间：6-按年 2-按月                 
    @StartTime DATETIME ,   
    @EndTime DATETIME ,   
    @SortField VARCHAR(100) ,   
    @SortMode VARCHAR(20) ,   
    @TopNum INT ,   
    @StatisticsPointer SMALLINT--统计指标 1.新增客户数 2.新进货客户数 3.新进产品数 4.新进终端数  5.新开发产品终端数                   
AS    
    BEGIN                                                                     
        SET NOCOUNT ON ;                                                     
        DECLARE @sWhere NVARCHAR(MAX)           
        DECLARE @sSql NVARCHAR(MAX)                                                   
        DECLARE @sOrder NVARCHAR(200)                                                   
                                                                       
        SET @sWhere = '  '           
             
        IF ( @CusType > -1 )    
            BEGIN                                                   
                SET @sWhere = @sWhere + ' AND '                                                   
            END                 
                 
        IF ( @CustomerID > -1 )    
            BEGIN                                                   
                SET @sWhere = @sWhere + ' AND '                                                   
            END                                                 
        IF ( @SortField = ''   
             OR @SortField IS NULL   
             OR @SortMode = ''   
             OR @SortMode IS NULL   
           )    
            BEGIN                                                                   
                SET @sOrder = N' B.TotalNum DESC '                                                                   
            END                                                  
        ELSE    
            BEGIN                                                     
                SET @sOrder = N' ' + @SortField + ' ' + @SortMode + ''                                                            
            END                                                     
        IF ( @DateType = 6 )--按年(当年份一样时,只显示该年)                       
            BEGIN                       
                DECLARE @str NVARCHAR(MAX)                       
                SET @str = [dbo].[uf_getStr](@DateType, @StartTime, @EndTime)                     
                IF ( @StatisticsPointer = 1 )--新增客户数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=VCALM.InUser) 
 
--AND SD1.FathIDPath like SD.FathIDPath+'',%''
AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID)'           
                 
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
           
                        SET @sSql = N'         
 WITH    DepTable         
          AS ( SELECT   SD.DepID AS UserID ,         
                        SD.DepName ,         
                        SD.DepPrincipal AS SaleUserName ,         
                        Num = CAST(( SELECT COUNT(CustomerID)         
                        FROM   dbo.VCustomerAndLinkManV20 VCALM         
        WITH ( NOLOCK )         
                                            LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
 WITH ( NOLOCK ) ON VCALM.InUser = VSDRU.UserID         
                                     WHERE  VCALM.CussState = 1         
                                            AND VCALM.CusDelState = 1         
                                            AND VCALM.InDate >= DF.BeginDate         
                                            AND VCALM.InDate < DF.EndDate'   
                            + @sWhere   
                            + '           
                                   ) AS DECIMAL(18, 0)) ,         
                        DF.DataID         
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,         
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,         
                                                        @EndTime) DF ,         
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,         
                                                              @DepID,         
                                                              @DeptLevel) DU         
               WHERE    SD.DepDelState = 1         
                        AND SD.DepIsState = 1         
                        AND DU.DataID = SD.DepID         
             )                 
                 
  SELECT  TOP ( @TopNum )                         
    UserID ,                         
    DepName,                             
    SaleUserName ,                             
    ' + @str + '                             
  FROM DepTable A PIVOT                                       
    ( SUM(A.Num) FOR A.DataID IN ( ' + @str + ' ) ) AS pvt                       
     ORDER BY UserID ASC;'                                   
                    END                           
                IF ( @StatisticsPointer = 2 )--新进货客户数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.OrderSaleUser)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID) '                 
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                  
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                        SET @sSql = N'         
 WITH    SaleManTable         
          AS ( SELECT   SD.DepName ,         
                        SD.DepID AS UserID ,         
                SD.DepPrincipal AS SaleUserName ,         
                        DF.DataID ,         
                        Num = CAST(( SELECT COUNT(A.CustomerID)         
                                     FROM   ( SELECT    A.OrderSaleUser ,         
                                                        A.CustomerID         
                                              FROM      dbo.CustomerProducPrice A         
     WITH ( NOLOCK )    
              INNER JOIN dbo.VCustomerAndLinkManV20 VCALM                 
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID                 
                       INNER JOIN dbo.VProductInfoView VPIV                 
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID         
                                                        LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
                                                        WITH ( NOLOCK ) ON A.OrderSaleUser = VSDRU.UserID         
                                              WHERE     A.FirstStockDate >= DF.BeginDate         
                                                        AND A.FirstStockDate < DF.EndDate'   
                            + @sWhere   
                            + '         
                                              GROUP BY  A.OrderSaleUser ,         
                                                        A.CustomerID         
                                            ) A         
                                   ) AS DECIMAL(18, 0))         
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,         
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,         
                                                        @EndTime) DF ,         
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,         
                                                              @DepID,         
                                                              @DeptLevel) DU         
               WHERE    SD.DepDelState = 1         
                        AND SD.DepIsState = 1         
                        AND DU.DataID = SD.DepID         
             )              
                 
             SELECT  TOP ( @TopNum )                         
    UserID ,                         
    DepName,                             
    SaleUserName ,                             
    ' + @str + '                             
  FROM SaleManTable A PIVOT                                       
    ( SUM(A.Num) FOR A.DataID IN ( ' + @str + ' ) ) AS pvt                       
     ORDER BY UserID ASC;'                  
                    END             
                IF ( @StatisticsPointer = 3 )--新进产品数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.OrderSaleUser)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID)'                 
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                  
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '          
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                        SET @sSql = N'WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
                        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,          
                        DF.DataID,                 
                        Num = CAST(( SELECT COUNT(A.ProductID)                 
                                     FROM   ( SELECT    A.OrderSaleUser ,                 
                                                        A.ProductID                 
                                              FROM      dbo.CustomerProducPrice A                
                                                        WITH ( NOLOCK )                 
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM                 
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID                 
   INNER JOIN dbo.VProductInfoView VPIV                 
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID           
                                                        LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
                                                        WITH ( NOLOCK ) ON A.OrderSaleUser = VSDRU.UserID                
                                       WHERE     A.FirstStockDate >= DF.BeginDate                 
                                                        AND A.FirstStockDate < DF.EndDate '   
                            + @sWhere   
                            + '                 
                                              GROUP BY  A.OrderSaleUser ,                 
                                                        A.ProductID                 
                                            ) A                 
                                   ) AS DECIMAL(18, 0))                 
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                              @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
                        AND SD.DepIsState = 1           
                        AND DU.DataID = SD.DepID            
             )                 
                 
             SELECT  TOP ( @TopNum )                         
    UserID ,                         
    DepName,                             
    SaleUserName ,                             
    ' + @str + '                             
  FROM SaleManTable A PIVOT                                       
    ( SUM(A.Num) FOR A.DataID IN ( ' + @str + ' ) ) AS pvt                       
     ORDER BY UserID ASC;'                  
                    END             
                IF ( @StatisticsPointer = 4 )--新进终端数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.SaleUserID)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = A.DepID)'                 
                        IF ( @HospitalID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                       
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                    
                            END             
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                   
                        SET @sSql = N'                 
 WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
                        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,           
                        DF.DataID,                 
                        Num = CAST(( SELECT COUNT(A.HospitalID)                 
                                     FROM   (SELECT    A.DepID ,             
                                                        A.HospitalID             
                                              FROM      dbo.CusProHospital A             
                                                        WITH ( NOLOCK )             
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM             
WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID             
                                                        INNER JOIN dbo.VProductInfoView VPIV             
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID 
                                              WHERE     A.CusProHosInfoType = 5             
                                                        AND A.CusDelState = 1             
                                                        AND A.CusUserIsState = 1             
                                                        AND A.FirstDate >= DF.BeginDate             
                                                        AND A.FirstDate < DF.EndDate                 
           ' + @sWhere   
                            + '                
                            GROUP BY  A.DepID ,A.HospitalID) A                 
               ) AS DECIMAL(18, 0))                 
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                              @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
                        AND SD.DepIsState = 1      
                        AND DU.DataID = SD.DepID           
             )                   
  SELECT  TOP ( @TopNum )              
    UserID ,                         
    DepName,                             
    SaleUserName ,                             
    ' + @str + '                             
  FROM SaleManTable A PIVOT                                       
    ( SUM(A.Num) FOR A.DataID IN ( ' + @str + ' ) ) AS pvt                       
     ORDER BY UserID ASC;'                  
                    END             
                IF ( @StatisticsPointer = 5 )--新开发产品终端数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.SaleUserID)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = A.DepID)'                 
                        IF ( @HospitalID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                      
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END             
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                   
                        SET @sSql = N'                 
 WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
                        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,           
                        DF.DataID,                  
                        Num = CAST(( SELECT COUNT(A.HospitalID)            
                                     FROM   (SELECT    A.DepID ,             
                                                        A.ProductID ,             
                                                        A.HospitalID             
                                              FROM      dbo.CusProHospital A             
                                                        WITH ( NOLOCK )             
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM             
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID             
                                                        INNER JOIN dbo.VProductInfoView VPIV             
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID 
                                              WHERE     A.CusProHosInfoType = 5             
                                                        AND A.CusDelState = 1             
                                       AND A.CusUserIsState = 1             
                                                        AND A.FirstDate >= DF.BeginDate             
                                                        AND A.FirstDate < DF.EndDate                 
           ' + @sWhere   
                            + '                
                            GROUP BY  A.DepID ,A.ProductID,A.HospitalID) A                 
                                   ) AS DECIMAL(18, 0))                  
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                              @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
             AND SD.DepIsState = 1           
                        AND DU.DataID = SD.DepID           
             )                   
  SELECT  TOP ( @TopNum )                         
    UserID ,                         
  DepName,                             
    SaleUserName ,                             
    ' + @str + '                             
  FROM SaleManTable A PIVOT                                       
    ( SUM(A.Num) FOR A.DataID IN ( ' + @str + ' ) ) AS pvt                       
     ORDER BY UserID ASC;'                  
                    END                     
            END                     
        IF ( @DateType = 2 )--按月                        
            BEGIN                                   
                IF ( @StatisticsPointer = 1 )--新增客户数                                   
                    BEGIN                 
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=VCALM.InUser)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID)'           
                 
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
           
                        SET @sSql = N'         
 WITH    DepTable         
          AS ( SELECT   SD.DepID AS UserID ,         
                        SD.DepName ,         
                        SD.DepPrincipal AS SaleUserName ,         
                        Num = CAST(( SELECT COUNT(CustomerID)         
                                     FROM   dbo.VCustomerAndLinkManV20 VCALM         
                                            WITH ( NOLOCK )         
                                            LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
                                            WITH ( NOLOCK ) ON VCALM.InUser = VSDRU.UserID         
                                     WHERE  VCALM.CussState = 1         
                                            AND VCALM.CusDelState = 1         
                                            AND VCALM.InDate >= DF.BeginDate         
                                            AND VCALM.InDate < DF.EndDate'   
                            + @sWhere   
                            + '           
                                   ) AS DECIMAL(18, 0)) ,         
                        DF.DataID         
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,         
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,         
                                                        @EndTime) DF ,         
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,         
                                                              @DepID,         
                                                              @DeptLevel) DU         
               WHERE    SD.DepDelState = 1         
                        AND SD.DepIsState = 1         
                        AND DU.DataID = SD.DepID         
             )           
                             
       SELECT  TOP ( @TopNum )           
   UserID ,           
   DepName,           
            SaleUserName ,           
            January ,            
            February ,                             
            March ,                             
            April ,                             
            May ,                             
            June ,                             
            July ,                             
            August ,                             
            September ,                             
            October ,                             
            November ,                             
            December ,                             
            TotalNum                             
    FROM    ( SELECT                              
                        UserID ,                         
      DepName,                             
                        SaleUserName ,                             
                        [1] AS January ,                             
                        [2] AS February ,                             
                        [3] AS March ,                             
                        [4] AS April ,                             
                        [5] AS May ,                             
                        [6] AS June ,                             
                        [7] AS July ,                             
                        [8] AS August ,                             
                        [9] AS September ,                             
                        [10] AS October ,                   
                        [11] AS November ,                             
                        [12] AS December ,                             
                        ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)                             
                        + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0)                             
                        + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0)                             
                        + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS TotalNum                             
              FROM      DepTable A PIVOT                                       
   ( SUM(A.Num) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],                             
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt                                  ) B ORDER BY   '   
                            + @sOrder + ' ;'                                 
                    END                            
                IF ( @StatisticsPointer = 2 )--新进货客户数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.OrderSaleUser)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID)'                 
                        IF ( @ProductID > -1 )  
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                  
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END           
                        SET @sSql = N'         
 WITH    SaleManTable         
          AS ( SELECT   SD.DepName ,         
                        SD.DepID AS UserID ,         
                        SD.DepPrincipal AS SaleUserName ,         
                        DF.DataID ,         
               Num = CAST(( SELECT COUNT(A.CustomerID)         
                                     FROM   ( SELECT    A.OrderSaleUser ,         
                                                        A.CustomerID         
                                              FROM      dbo.CustomerProducPrice A         
                                                        WITH ( NOLOCK )         
              INNER JOIN dbo.VCustomerAndLinkManV20 VCALM                 
                                      WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID                 
                                                        INNER JOIN dbo.VProductInfoView VPIV                 
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID         
                                                        LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
                                                        WITH ( NOLOCK ) ON A.OrderSaleUser = VSDRU.UserID         
                                              WHERE     A.FirstStockDate >= DF.BeginDate         
                                                        AND A.FirstStockDate < DF.EndDate'   
                            + @sWhere   
                            + '         
                                              GROUP BY  A.OrderSaleUser ,         
  A.CustomerID         
                                            ) A         
                                   ) AS DECIMAL(18, 0))         
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,         
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,         
                                                        @EndTime) DF ,         
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,         
                                                              @DepID,         
                                                              @DeptLevel) DU         
               WHERE    SD.DepDelState = 1         
                        AND SD.DepIsState = 1         
                        AND DU.DataID = SD.DepID         
             )                
                              
            SELECT  TOP ( @TopNum )                         
   UserID ,                         
   DepName,                             
            SaleUserName ,                
            January ,                             
            February ,                             
            March ,                             
            April ,                   
            May ,                             
            June ,                             
           July ,                             
            August ,                             
            September ,                             
            October ,                             
            November ,                             
            December ,                             
            TotalNum                             
    FROM    ( SELECT                              
                        UserID ,                         
      DepName,                             
                        SaleUserName ,                             
                        [1] AS January ,                             
                        [2] AS February ,                             
                        [3] AS March ,                             
                        [4] AS April ,                             
                        [5] AS May ,                             
                        [6] AS June ,                             
                        [7] AS July ,                             
                        [8] AS August ,                             
                        [9] AS September ,                             
                        [10] AS October ,                             
                        [11] AS November ,                             
                        [12] AS December ,                                                    ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)                             
                        + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0)                             
                        + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0)                             
                        + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS TotalNum                             
              FROM      SaleManTable A PIVOT                                       
   ( SUM(A.Num) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],                         
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt                             
            ) B ORDER BY   ' + @sOrder + ' ;'                  
                    END             
                IF ( @StatisticsPointer = 3 )--新进产品数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.OrderSaleUser)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = VSDRU.DepID)'                 
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                   
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                        SET @sSql = N'WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,           
                        DF.DataID,                 
                        Num = CAST(( SELECT COUNT(A.ProductID)                 
                                     FROM   ( SELECT    A.OrderSaleUser ,                 
                                                        A.ProductID                 
                                              FROM      dbo.CustomerProducPrice A                 
                                                        WITH ( NOLOCK )                 
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM                 
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID                 
                                                        INNER JOIN dbo.VProductInfoView VPIV                 
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID           
                                                        LEFT JOIN dbo.VSysDepRegionUserV20 VSDRU         
                                                        WITH ( NOLOCK ) ON A.OrderSaleUser = VSDRU.UserID                
                                              WHERE     A.FirstStockDate >= DF.BeginDate                 
                                                        AND A.FirstStockDate < DF.EndDate '   
                            + @sWhere   
                            + '                 
                                GROUP BY  A.OrderSaleUser ,                 
                                                        A.ProductID                 
                                            ) A                 
                                   ) AS DECIMAL(18, 0))                 
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                  @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
                        AND SD.DepIsState = 1           
                        AND DU.DataID = SD.DepID            
             )                 
                              
            SELECT  TOP ( @TopNum )                         
   UserID ,                         
   DepName,                             
            SaleUserName ,                             
            January ,                             
            February ,                             
            March ,                             
            April ,                             
            May ,                             
            June ,                             
            July ,                             
            August ,                             
            September ,                             
            October ,                             
            November ,                             
            December ,                             
            TotalNum                        
    FROM    ( SELECT                              
                        UserID ,                         
      DepName,                             
                        SaleUserName ,                             
                        [1] AS January ,                             
                        [2] AS February ,                             
                        [3] AS March ,                             
                        [4] AS April ,                             
                        [5] AS May ,                             
                        [6] AS June ,                             
                        [7] AS July ,                             
                        [8] AS August ,                             
                        [9] AS September ,                             
                        [10] AS October ,                             
                        [11] AS November ,                             
                        [12] AS December ,                             
                        ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)                             
                        + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0)                             
                        + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0)                             
                        + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS TotalNum                             
              FROM      SaleManTable A PIVOT                                       
   ( SUM(A.Num) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],                             
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt                             
            ) B ORDER BY   ' + @sOrder + ' ;'                  
                    END             
                IF ( @StatisticsPointer = 4 )--新进终端数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.SaleUserID) 
 AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = A.DepID) '                 
                        IF ( @HospitalID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                        
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END             
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                        SET @sSql = N'                 
 WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
                        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,           
                        DF.DataID,                 
                        Num = CAST(( SELECT COUNT(A.HospitalID)                 
                                     FROM   (SELECT    A.DepID ,             
                         A.HospitalID             
                                              FROM      dbo.CusProHospital A             
     WITH ( NOLOCK )             
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM             
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID             
                                                        INNER JOIN dbo.VProductInfoView VPIV             
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID           
--                                                        LEFT JOIN dbo.SysDepartment SD1           
--              WITH ( NOLOCK ) ON A.DepID= SD1.DepID             
                                              WHERE     A.CusProHosInfoType = 5             
                                                        AND A.CusDelState = 1             
                                                        AND A.CusUserIsState = 1             
                                                        AND A.FirstDate >= DF.BeginDate             
                                                        AND A.FirstDate < DF.EndDate                 
           ' + @sWhere   
                            + '                
                         GROUP BY  A.DepID ,A.HospitalID) A                 
               ) AS DECIMAL(18, 0))                 
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                              @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
        AND SD.DepIsState = 1           
                        AND DU.DataID = SD.DepID           
             )                  
                             
       SELECT  TOP ( @TopNum )                         
   UserID ,                         
   DepName,                             
            SaleUserName ,                             
            January ,                             
            February ,                             
            March ,                             
            April ,                             
            May ,                             
            June ,                             
            July ,                             
            August ,                             
            September ,                             
            October ,                             
          November ,                             
         December ,                             
            TotalNum                             
    FROM    ( SELECT                              
                        UserID ,                         
      DepName,                             
                        SaleUserName ,                                        [1] AS January ,                             
                        [2] AS February ,                             
                        [3] AS March ,                             
                        [4] AS April ,                             
                        [5] AS May ,                             
                        [6] AS June ,                             
                        [7] AS July ,                             
                        [8] AS August ,                             
                        [9] AS September ,                             
                        [10] AS October ,                             
                        [11] AS November ,                             
                        [12] AS December ,                             
                        ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)                             
                        + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0)                             
         + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0)                             
                        + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS TotalNum                             
              FROM      SaleManTable A PIVOT                                       
   ( SUM(A.Num) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],                             
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt                             
            ) B ORDER BY   ' + @sOrder + ' ;'                 
                    END             
                IF ( @StatisticsPointer = 5 )--新开发产品终端数                                   
                    BEGIN           
                        SET @sWhere = @sWhere   
                            + ' AND EXISTS( SELECT TOP 1 1 FROM dbo.VSysUserDataRight SUDS                                                  
      WHERE AND SUDS.ViewedUserID=A.SaleUserID)            
   AND EXISTS( SELECT TOP 1 1 FROM [dbo].[UF_XA_UserDataSearch_DepartmentSearch_V40](SD.DepID) ds                 
         WHERE ds.DataID = A.DepID)'                 
                        IF ( @HospitalID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                                  
                        IF ( @ProductID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END             
                        IF ( @ProTypeID > -1 )    
                            BEGIN             
                                SET @sWhere = @sWhere   
                                    + ' AND '             
                            END           
                        IF ( @SaleUserID > -1 )    
                            BEGIN                                                   
                                SET @sWhere = @sWhere   
                                    + ' AND '                                                   
                            END                 
                        SET @sSql = N'                 
 WITH    SaleManTable                 
          AS ( SELECT   SD.DepName ,                 
                        SD.DepID AS UserID ,                 
                        SD.DepPrincipal AS SaleUserName,           
                        DF.DataID,                  
                        Num = CAST(( SELECT COUNT(A.HospitalID)                 
                                     FROM   (SELECT    A.DepID ,             
                                                        A.ProductID ,             
                                                        A.HospitalID             
                                              FROM      dbo.CusProHospital A             
                                                        WITH ( NOLOCK )             
                                                        INNER JOIN dbo.VCustomerAndLinkManV20 VCALM             
                                                        WITH ( NOLOCK ) ON A.CustomerID = VCALM.CustomerID             
                                                        INNER JOIN dbo.VProductInfoView VPIV             
                                                        WITH ( NOLOCK ) ON A.ProductID = VPIV.ProductID           
--                                                        LEFT JOIN dbo.SysDepartment SD1           
--              WITH ( NOLOCK ) ON A.DepID = SD1.DepID             
                                              WHERE     A.CusProHosInfoType = 5             
                                                        AND A.CusDelState = 1             
                                                        AND A.CusUserIsState = 1             
                                                        AND A.FirstDate >= DF.BeginDate             
                                                        AND A.FirstDate < DF.EndDate                 
           ' + @sWhere   
                            + '                
                            GROUP BY  A.DepID ,A.ProductID,A.HospitalID) A                 
                                   ) AS DECIMAL(18, 0))                  
               FROM     dbo.SysDepartment SD WITH ( NOLOCK ) ,           
                        dbo.[UF_XA_GetDatePartTable_21](@DateType, @StartTime,           
                                                        @EndTime) DF ,           
                        dbo.UF_XA_Report_GetSortDeptListByContains(@CurrentUserID,           
                                                              @DepID,           
                                                              @DeptLevel) DU           
               WHERE    SD.DepDelState = 1           
                        AND SD.DepIsState = 1           
                        AND DU.DataID = SD.DepID           
             )                 
                             
       SELECT  TOP ( @TopNum )                         
   UserID ,                         
   DepName,                             
            SaleUserName ,                             
            January ,                             
            February ,                             
            March ,                             
            April ,                             
            May ,                             
            June ,                             
            July ,                             
            August ,                             
  September ,                          
            October ,                             
            November ,                             
            December ,                             
            TotalNum                             
    FROM    ( SELECT                              
                        UserID ,                         
      DepName,                             
                        SaleUserName ,                                        [1] AS January ,                             
                        [2] AS February ,                             
                        [3] AS March ,                             
                        [4] AS April ,                             
                        [5] AS May ,                             
                        [6] AS June ,                             
                        [7] AS July ,                             
                        [8] AS August ,                             
                        [9] AS September ,                        
                        [10] AS October ,                             
                        [11] AS November ,                             
                        [12] AS December ,                             
                        ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0)                             
                        + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0)                             
                        + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0)                             
                        + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS TotalNum                             
              FROM      SaleManTable A PIVOT                           
   ( SUM(A.Num) FOR A.DataID IN ( [1], [2], [3], [4], [5], [6],                             
                                               [7], [8], [9], [10], [11], [12] ) ) AS pvt                             
            ) B ORDER BY   ' + @sOrder + ' ;'                 
                    END                
            END                                           
    END                                                  
    EXEC SP_EXECUTESQL @sSql, INT ,           
 @DeptLevel INT,                 
 @DepID INT ,                 
 @SaleUserID INT ,                 
 @CusType SMALLINT ,                 
 @CustomerID INT ,                 
 @ProTypeID INT,                 
 @ProductID INT,                 
 @HospitalID INT,                 
 @DateType INT ,--统计区间：6-按年 2-按月                 
 @StartTime DATETIME ,                 
 @EndTime DATETIME ,                 
 @SortField VARCHAR(100) ,                 
 @SortMode VARCHAR(20) ,                 
 @TopNum INT ,   
 @StatisticsPointer SMALLINT', @CurrentUserID, @DeptLevel, @DepID, @SaleUserID,   
        @CusType, @CustomerID, @ProTypeID, @ProductID, @HospitalID, @DateType,   
        @StartTime, @EndTime, @SortField, @SortMode, @TopNum,   
        @StatisticsPointer ;

转载于:https://www.cnblogs.com/ywblog/archive/2013/01/05/2845603.html

你可能感兴趣的文章
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-11-14 23:11:49   当前IP: 18.223.206.144   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我