根据月份查询用户的收入情况的存储过程

------------------------------------------------------------------------------
---说明：.根据月份查询用户的收入情况
---创建人：self
---创建时间：2019-04-17
------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[P_WillPayByMonth]
	@month VARCHAR(7)
AS 
BEGIN
/**创建临时表***/
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N‘tempdb..#tmp‘) and type=‘U‘)
DROP TABLE #tmp
 CREATE TABLE #tmp
(
  id INT,
  login_name VARCHAR(64),
  yaoqingma_self VARCHAR(10),
  yaoqingma VARCHAR(10),
  effect_sum DECIMAL(8,2),
  yongjin_sum DECIMAL(8,2),
  [level] INT,
  isLeader INT
)
DECLARE @yaoqingma_pid VARCHAR(64) 
DECLARE c_DpCursor CURSOR

FOR
    SELECT  yaoqingma_self
    FROM    [dbo].sys_user
    
OPEN c_DpCursor
FETCH NEXT FROM c_DpCursor INTO @yaoqingma_pid
WHILE @@fetch_status = 0 
    BEGIN
  --  SET @month=‘2019-04‘
        PRINT ( @yaoqingma_pid ) ;
        WITH    temp (id, login_name, [yaoqingma_self], [yaoqingma],effect_sum,yongjin_sum, [level], isLeader )
                  AS ( 
                  -- 一级订单
                  SELECT @@fetch_status AS id,  a.login_name ,
                                a.yaoqingma_self ,
                                a.yaoqingma ,
                                o.effect_first AS effect_sum,
                                o.yongjin_first,
                                1 ,
                                1
                       FROM     dbo.sys_user a LEFT JOIN 
                       (SELECT SUM(effect_first)effect_first,SUM(yongjin_first)yongjin_first,userid FROM dbo.sys_taobao_order 
                        where SUBSTRING(order_time ,1,7) = @month GROUP BY userid  ) o
                        ON a.login_name = o.userid
                       WHERE    [yaoqingma_self] = @yaoqingma_pid
                       UNION ALL
                       -- 二级订单
                  SELECT  @@fetch_status AS id, a.login_name ,
                                a.yaoqingma_self ,
                                a.yaoqingma ,
                                o.effect_second AS effect_sum,
                                o.yongjin_second,
                                1 ,
                                0
                       FROM     dbo.sys_user a LEFT JOIN 
                       (SELECT SUM(effect_second)effect_second,SUM(yongjin_second)yongjin_second,userid FROM dbo.sys_taobao_order 
                        where SUBSTRING(order_time ,1,7) = @month GROUP BY userid  ) o
                        ON a.login_name = o.userid
                       WHERE    [yaoqingma] = @yaoqingma_pid
                       UNION ALL
                       -- 三级订单
                       SELECT @@fetch_status AS id,  a.login_name ,
                                a.yaoqingma_self ,
                                a.yaoqingma ,
                                o.effect_third AS effect_sum,
                                o.yongjin_third,
                                2 ,
                                0
                       FROM     dbo.sys_user a
                       INNER join sys_user b on a.yaoqingma=b.yaoqingma_self
                       LEFT JOIN 
                       (SELECT SUM(effect_third)effect_third,SUM(yongjin_third)yongjin_third,userid FROM dbo.sys_taobao_order
                        where SUBSTRING(order_time ,1,7) = @month GROUP BY userid  ) o
                        ON a.login_name = o.userid
                        WHERE a.yaoqingma IN(SELECT yaoqingma_self FROM dbo.sys_user WHERE yaoqingma=@yaoqingma_pid)  
                     )
            /** 将查询的结果放入临时表中**/
  INSERT INTO #tmp  SELECT  *
            FROM    temp
        FETCH NEXT FROM c_DpCursor INTO @yaoqingma_pid
       -- SELECT login_name, [yaoqingma_self], [yaoqingma], 0 AS [level], 1 As isLeader FROM dbo.sys_user -- WHERE yaoqingma=‘NEDW4Q‘
    END
    /**查询临时表的内容***/
     SELECT * FROM #tmp
CLOSE c_DpCursor
DEALLOCATE c_DpCursor
--for example： EXEC [dbo].[P_WillPayByMonth] @month = N‘2019-03‘
END