mysql 查询昨日收益_根据月份查询用户的收入情况的存储过程

------------------------------------------------------------------------------

---说明：.根据月份查询用户的收入情况

---创建人：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