------------------------------------------------------------------------------
---说明:.根据月份查询用户的收入情况
---创建人: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